package com.mzj.saas.mservice.house.service;

import com.mzj.saas.commons.DownloadUtil;
import com.mzj.saas.commons.Export2Util;
import com.mzj.saas.commons.HouseApiData;
import com.mzj.saas.commons.ResultBean;
import com.mzj.saas.commons.enums.BacklogItemTypeEnum;
import com.mzj.saas.commons.enums.HouseAuditStatusEnum;
import com.mzj.saas.commons.enums.HouseBindStatusEnum;
import com.mzj.saas.commons.enums.HouseMatchStatusEnum;
import com.mzj.saas.commons.vo.redis.TokenRedisVo;
import com.mzj.saas.mservice.application.entity.HouseAuditSet;
import com.mzj.saas.mservice.application.repository.HouseAuditSetRepository;
import com.mzj.saas.mservice.area.entity.Area;
import com.mzj.saas.mservice.area.repository.AreaRepository;
import com.mzj.saas.mservice.backlog.entity.BacklogItem;
import com.mzj.saas.mservice.backlog.repository.BacklogItemRepository;
import com.mzj.saas.mservice.community.entity.Community;
import com.mzj.saas.mservice.community.entity.CommunityBuild;
import com.mzj.saas.mservice.community.entity.CommunityBuildUnit;
import com.mzj.saas.mservice.community.entity.HouseType;
import com.mzj.saas.mservice.community.repository.CommunityBuildRepository;
import com.mzj.saas.mservice.community.repository.CommunityBuildUnitRepository;
import com.mzj.saas.mservice.community.repository.CommunityRepository;
import com.mzj.saas.mservice.community.repository.HouseTypeRepository;
import com.mzj.saas.mservice.community.service.AppCommunityService;
import com.mzj.saas.mservice.department.entity.Department;
import com.mzj.saas.mservice.department.repository.DepartmentRepository;
import com.mzj.saas.mservice.eligible.entity.DepartmentEnterpriseTagRef;
import com.mzj.saas.mservice.eligible.entity.DepartmentHierarchy;
import com.mzj.saas.mservice.eligible.entity.Eligible;
import com.mzj.saas.mservice.eligible.entity.WaitingRentRecord;
import com.mzj.saas.mservice.eligible.repository.DepartmentEnterpriseTagRefRepository;
import com.mzj.saas.mservice.eligible.repository.DepartmentHierarchyRepository;
import com.mzj.saas.mservice.eligible.repository.WaitingRentRecordRepository;
import com.mzj.saas.mservice.house.entity.Cohabit;
import com.mzj.saas.mservice.house.entity.House;
import com.mzj.saas.mservice.house.repository.CohabitRepository;
import com.mzj.saas.mservice.house.repository.HouseRepository;
import com.mzj.saas.mservice.house.vo.*;
import com.mzj.saas.mservice.sys.entity.AppUser;
import com.mzj.saas.mservice.sys.repository.AppUserRepository;
import com.mzj.saas.mservice.sys.utils.SqlUtils;
import com.mzj.saas.redis.RedisService;
import net.sourceforge.pinyin4j.PinyinHelper;
import net.sourceforge.pinyin4j.format.HanyuPinyinCaseType;
import net.sourceforge.pinyin4j.format.HanyuPinyinOutputFormat;
import net.sourceforge.pinyin4j.format.HanyuPinyinToneType;
import net.sourceforge.pinyin4j.format.exception.BadHanyuPinyinOutputFormatCombination;
import org.apache.commons.lang3.RandomUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.data.redis.core.StringRedisTemplate;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

@Service
public class HouseService {
    private static final Logger log = LoggerFactory.getLogger(HouseService.class);

    private static final String HOUSE_COUNT = "SELECT COUNT(1) ";
    private static final String HOUSE_COLUM = "SELECT *";
    private static final String HOUSE_FROM_MONITOR = " FROM saas_house h";
    private static final String ORDER_ORDER = " ORDER BY h.create_time DESC";
    private static final String SQL_PAGE = " LIMIT ? OFFSET ?";

    @Autowired
    private HouseRepository houseRepository;
    @Autowired
    @Qualifier("stringRedisTemplate")
    private StringRedisTemplate redisTemplate;
    @Autowired
    private AreaRepository areaRepository;
    @Autowired
    private DepartmentRepository departmentRepository;
    @Autowired
    private RedisService redisService;
    @Autowired
    private DepartmentHierarchyRepository departmentHierarchyRepository;
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private CommunityRepository communityRepository;
    @Autowired
    private CommunityBuildRepository communityBuildRepository;
    @Autowired
    private CommunityBuildUnitRepository communityBuildUnitRepository;
    @Autowired
    private DepartmentEnterpriseTagRefRepository departmentEnterpriseTagRefRepository;
    @Autowired
    private BacklogItemRepository backlogItemRepository;
    @Autowired
    private HouseAuditSetRepository houseAuditSetRepository;
    @Autowired
    private CohabitRepository cohabitRepository;
    @Autowired
    private AppUserRepository appUserRepository;
    @Autowired
    private WaitingRentRecordRepository waitingRentRecordRepository;
    @Autowired
    private HouseTypeRepository houseTypeRepository;
    @Autowired
    private AppCommunityService appCommunityService;

    /**
     * 公租房列表,查询公租房列表,实现了已录入的公租房列表的显示和公租房的搜索，可以根据公租房的所有字段进行搜索，从而找出对应的公租房信息
     *
     * @param accessToken          权限鉴定
     * @param pageNumber           当前页
     * @param pageSize             页数
     * @param residenceCommunityId 小区id
     * @param buildingNum          楼栋
     * @param unit                 单元
     * @param roomNumber           房号
     * @param districtId           区
     * @param bindStatus           产权人绑定状态:0未绑定 1绑定
     * @param matchStatus          配租状态：0未配租 1可配租 2已配租
     * @param leaseStatus          出租状态：0未出租 1已出租
     * @param pwdLockStatus        密码锁授权：0未授权 1已授权 4已冻结
     * @param houseNature          性质:1公租房、2廉租房、3商品房、4经济适用房、5其它
     * @param checkStatus          核查状态：0未核查、1已核查
     * @param orgId                组织id
     * @param search               搜索条件
     * @param houseId              房屋id
     * @param floor                楼层
     * @param area                 面积
     * @param decorateType         装修
     * @param provinceId           省
     * @param cityId               市
     * @param streetId             街道
     * @param communityId          社区
     * @param address              地址
     * @param auditStatus          房源审核状态:0未审核 1已审核
     * @param room                 室
     * @param hall                 厅
     * @param toilet               卫
     * @param houseIdList          房屋id集合
     * @return
     */
    public ResultBean<HouseApiData<HouseVO>> houseList(String accessToken, Integer pageNumber, Integer pageSize,
                                                       String residenceCommunityId, Long buildingNum, Long unit, String roomNumber, Long districtId,
                                                       Integer bindStatus, Integer matchStatus, Integer leaseStatus, Integer pwdLockStatus,
                                                       Integer houseNature, Integer checkStatus, Long orgId, String search,
                                                       String houseId, Integer floor, BigDecimal area, Integer decorateType, Long provinceId,
                                                       Long cityId, Long streetId, Long communityId, String address, Integer auditStatus,
                                                       Integer room, Integer hall, Integer toilet, List<String> houseIdList) {
        //设置统一返回结果
        ResultBean<HouseApiData<HouseVO>> resultBean = new ResultBean<>();
        resultBean.setCode("10000");
        resultBean.setMsg("10000");
        //存储公租房列表数据
        HouseApiData<HouseVO> apiData = new HouseApiData<>();
        resultBean.setResultData(apiData);
        try {
            //根据token获取用户信息
            TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
            //用户权限的鉴定
            if (tokenRedisVo == null) {
                return ResultBean.failedResultOfToken();
            }
            //获取用户ID
            String userId = tokenRedisVo.getId();
            //获取企业ID
            long enterpriseId = tokenRedisVo.getEnterpriseId();
            //根据企业ID查询出用户层级权限
            DepartmentHierarchy hierarchy = departmentHierarchyRepository.findByOrgIdAndStatus(enterpriseId, 1);
            if (hierarchy == null) {
                return ResultBean.failedResultWithMsg("用户所属机构未设置层级架构");
            }
            //获取企业用户ID
            Long id = hierarchy.getId();
            //根据企业用户ID获取公租房数据
            List<DepartmentEnterpriseTagRef> refs = departmentEnterpriseTagRefRepository.findByDepRefId(id);
            //获取企业标签集合
            List<Long> enterpriseTagIds = refs.stream().map(DepartmentEnterpriseTagRef::getTagId).distinct().collect(Collectors.toList());//用户企业标签

            //用户是否住建局，是否房源审核人
            long auditAreaId = 0L;
            //根据企业标签判读用户是否住建局，是否房源审核人
            if (enterpriseTagIds.contains(5L)) {
                //查询出审批人数据
                HouseAuditSet houseAuditSet = houseAuditSetRepository.findByAuditIdAndStatus(userId, 1);
                //如果审批人数据不为空
                if (houseAuditSet != null) {
                    //获取审批人ID
                    auditAreaId = houseAuditSet.getAreaId();
                }
            }

            //1、政府机构，根据区域标签所对应区域id查公租房
            Long areaId = null;
            String areaTag = "province_id";
            //判读区域标签所对应区域的id查公租房
            if (enterpriseTagIds.contains(3L)) {
                Long areaTagId = hierarchy.getTagId();//区域标签
                if (areaTagId == null) {
                    return ResultBean.failedResultWithMsg("机构所对应区域标签为空");
                }
                // 政府机构，根据区域标签所对应区域id查公租房
                if (areaTagId == 1) {
                    areaTag = "province_id";
                } else if (areaTagId == 2) {
                    areaTag = "city_id";
                } else if (areaTagId == 3) {
                    areaTag = "district_id";
                } else if (areaTagId == 4) {
                    areaTag = "street_id";
                } else if (areaTagId == 5) {
                    areaTag = "community_id";
                }
                areaId = hierarchy.getAreaId();//区域id
            }

            //2、产权单位，根据orgId查公租房
            Long hierarchyOrgId = null;
            //判读机构标签所对应区域的id查公租房
            if (enterpriseTagIds.contains(2L)) {
                hierarchyOrgId = hierarchy.getOrgId();
            }

            //3、物业，查已绑定该物业的小区的所有公租房
            List<String> residenceCommunityIds = new ArrayList<>();
            //判读小区标签所对应区域的id查公租房
            if (enterpriseTagIds.contains(4L)) {
                List<Community> communities = communityRepository.findByOrgIdAndStatus(enterpriseId, 1);
                residenceCommunityIds = communities.stream().map(Community::getId).distinct().collect(Collectors.toList());//小区id
            }
            //拼接区域为公租房的数据
            StringBuilder where = new StringBuilder();
            List<Object> args = new ArrayList<>();
            where.append(" where 1 = 1");
            where.append(" and (h." + areaTag + " = ? or h.org_id = ? or h.residence_community_id in ");
            args.add(areaId);
            args.add(hierarchyOrgId);
            //如果小区id不为空，那么就根据小区id查询出对应的公租房数据
            if (residenceCommunityIds != null && !residenceCommunityIds.isEmpty()) {
                where.append(SqlUtils.foreachIn(residenceCommunityIds.size()));
                where.append(")");
                args.addAll(residenceCommunityIds);
            //    如果没有查询为空
            } else {
                where.append("(?))");
                args.add(null);
            }

            where.append(" and h.house_nature = 1");
            //如果机构id不为空，那么就根据机构id查询出对应的公租房数据
            if (orgId != null) {
                where.append(" and h.org_id = ?");
                args.add(orgId);
            }
            //如果小区id不为空，那么就根据小区id查询出对应的公租房数据
            if (StringUtils.isNotBlank(residenceCommunityId)) {
                where.append(" and h.residence_community_id = ?");
                args.add(residenceCommunityId);
            }
            //如果楼栋层数不为空，那么就根据楼栋层数查询出对应的公租房数据
            if (buildingNum != null) {
                where.append(" and h.building_num = ?");
                args.add(buildingNum);
            }
            //如果单位不为空，那么就根据单位数查询出对应的公租房数据
            if (unit != null) {
                where.append(" and h.unit = ?");
                args.add(unit);
            }
            //如果房号不为空，那么就根据房号数查询出对应的公租房数据
            if (StringUtils.isNotBlank(roomNumber)) {
                where.append(" and h.room_number = ?");
                args.add(roomNumber);
            }
            //如果小区不为空，那么就根据小区查询出对应的公租房数据
            if (districtId != null) {
                where.append(" and h.district_id = ?");
                args.add(districtId);
            }
            //如果产权人绑定状态:0未绑定 1绑定 不为空，那么就根据产权人绑定状态查询出对应的公租房数据
            if (bindStatus != null) {
                where.append(" and h.bind_status = ?");
                args.add(bindStatus);
            }
            //如果配租状态：0未配租 1可配租 2已配租 不为空，那么就根据配租状态查询出对应的公租房数据
            if (matchStatus != null) {
                where.append(" and h.match_status = ?");
                args.add(matchStatus);
            }
            //如果出租状态：0未出租 1已出租 不为空，那么就根据出租状态查询出对应的公租房数据
            if (leaseStatus != null) {
                where.append(" and h.lease_status = ?");
                args.add(leaseStatus);
            }
            //如果密码锁授权：0未授权 1已授权 4已冻结 不为空，那么就根据密码锁授权查询出对应的公租房数据
            if (pwdLockStatus != null) {
                where.append(" and h.pwd_lock_status = ?");
                args.add(pwdLockStatus);
            }
            //如果性质:1公租房、2廉租房、3商品房、4经济适用房、5其它 不为空，那么就根据性质状态查询出对应的公租房数据
            if (houseNature != null) {
                where.append(" and h.house_nature = ?");
                args.add(houseNature);
            }
            //如果核查状态：0未核查、1已核查 不为空，那么就根据核查状态查询出对应的公租房数据
            if (checkStatus != null) {
                where.append(" and h.check_status = ?");
                args.add(checkStatus);
            }
            //如果搜索条件不为空，那么就根据搜索条件查询出对应的公租房数据
            if (StringUtils.isNotBlank(search)) {
                where.append(" AND (h.`id` LIKE ? or h.residence_community_name LIKE ?)");
                args.add("%" + search + "%");
                args.add("%" + search + "%");
            }
            //如果房屋id不为空，那么就根据房屋id查询出对应的公租房数据
            if (StringUtils.isNotBlank(houseId)) {
                where.append(" AND h.`id` LIKE ?");
                args.add("%" + houseId + "%");
            }
            //如果房屋id不为空，那么就根据房屋id查询出对应的公租房数据
            if (houseIdList != null && !houseIdList.isEmpty()) {
                where.append(" AND h.id in ").append(SqlUtils.foreachIn(houseIdList.size()));
                args.addAll(houseIdList);
            }
            //如果楼层不为空，那么就根据楼层数查询出对应的公租房数据
            if (floor != null) {
                where.append(" and h.floor = ?");
                args.add(floor);
            }
            //如果面积不为空，那么就根据面积查询出对应的公租房数据
            if (area != null) {
                where.append(" and h.area = ?");
                args.add(area);
            }
            //如果装修不为空，那么就根据装修查询出对应的公租房数据
            if (decorateType != null) {
                where.append(" and h.decorate_type = ?");
                args.add(decorateType);
            }
            //如果省份不为空，那么就根据省份查询出对应的公租房数据
            if (provinceId != null) {
                where.append(" and h.province_id = ?");
                args.add(provinceId);
            }
            //如果市区不为空，那么就根据市区查询出对应的公租房数据
            if (cityId != null) {
                where.append(" and h.city_id = ?");
                args.add(cityId);
            }
            //如果街道不为空，那么就根据街道查询出对应的公租房数据
            if (streetId != null) {
                where.append(" and h.street_id = ?");
                args.add(streetId);
            }
            //如果社区不为空，那么就根据社区查询出对应的公租房数据
            if (communityId != null) {
                where.append(" and h.community_id = ?");
                args.add(communityId);
            }
            //如果地址不为空，那么就根据地址查询出对应的公租房数据
            if (StringUtils.isNotBlank(address)) {
                where.append(" and h.address = ?");
                args.add("%" + address + "%");
            }
            //如果房源审核状态:0未审核 1已审核 不为空，那么就根据房源审核状态查询出对应的公租房数据
            if (auditStatus != null) {
                where.append(" and h.audit_status = ?");
                args.add(auditStatus);
            }
            //如果室不为空，那么就根据室查询出对应的公租房数据
            if (room != null) {
                where.append(" and h.room = ?");
                args.add(room);
            }
            //如果厅不为空，那么就根据厅查询出对应的公租房数据
            if (hall != null) {
                where.append(" and h.hall = ?");
                args.add(hall);
            }
            //如果卫不为空，那么就根据卫查询出对应的公租房数据
            if (toilet != null) {
                where.append(" and h.toilet = ?");
                args.add(toilet);
            }

            //总数
            Long total = jdbcTemplate.queryForObject(HOUSE_COUNT + HOUSE_FROM_MONITOR + where, args.toArray(), Long.class);
            if (total == 0) {
                return resultBean;
            }
            //已配租数
            Long isMatchTotal = jdbcTemplate.queryForObject(HOUSE_COUNT + HOUSE_FROM_MONITOR + where + " and h.match_status = 2", args.toArray(), Long.class);
            //空置房数
            Long unMatchTotal = total - isMatchTotal;

            //产权人id
            List<HouseVO> orgList = jdbcTemplate.query(HOUSE_COLUM + HOUSE_FROM_MONITOR + where, new BeanPropertyRowMapper<>(HouseVO.class), args.toArray());
            List<Long> orgIds = orgList.stream().filter(vo -> vo.getOrgId() != null).map(HouseVO::getOrgId).distinct().collect(Collectors.toList());
            //设置从第几条查询
            args.add(pageSize);
            //设置查询多少条数据
            args.add(pageNumber * pageSize);
            //查询出匹配的公租房数据
            List<HouseVO> houseList = jdbcTemplate.query(HOUSE_COLUM + HOUSE_FROM_MONITOR + where + ORDER_ORDER + SQL_PAGE,
                    new BeanPropertyRowMapper<>(HouseVO.class), args.toArray());
            //遍历公租房数据
            for (HouseVO vo : houseList) {
                //配租按钮状态：0不显示，1显示
                if (vo.getMatchStatus() != null && vo.getOrgId() != null && vo.getMatchStatus() == 1 && vo.getOrgId() == enterpriseId) {
                    vo.setRentStatus(1);
                } else {
                    vo.setRentStatus(0);
                }

                //审核标识：0不可审核，1可审核
                if (vo.getProvinceId() == auditAreaId || vo.getCityId() == auditAreaId || vo.getDistrictId() == auditAreaId
                        || vo.getStreetId() == auditAreaId || vo.getCommunityId() == auditAreaId) {
                    vo.setAuditFlag(1);
                } else {
                    vo.setAuditFlag(0);
                }
            }

            // 查询区域名称
            List<Long> ids = new ArrayList<>();
            //根据省份查询出公租房数据
            List<Long> provinceIds = houseList.stream().filter(vo -> vo.getProvinceId() != null).map(HouseVO::getProvinceId).distinct().collect(Collectors.toList());
            //根据市区查询出公租房数据
            List<Long> cityIds = houseList.stream().filter(vo -> vo.getCityId() != null).map(HouseVO::getCityId).distinct().collect(Collectors.toList());
            //根据区域查询出公租房数据
            List<Long> districtIds = houseList.stream().filter(vo -> vo.getDistrictId() != null).map(HouseVO::getDistrictId).distinct().collect(Collectors.toList());
            //根据街道查询出公租房数据
            List<Long> streetIds = houseList.stream().filter(vo -> vo.getStreetId() != null).map(HouseVO::getStreetId).distinct().collect(Collectors.toList());
            //根据社区查询出公租房数据
            List<Long> communityIds = houseList.stream().filter(vo -> vo.getCommunityId() != null).map(HouseVO::getCommunityId).distinct().collect(Collectors.toList());
            //把查询到的数据存储进去
            ids.addAll(provinceIds);
            ids.addAll(cityIds);
            ids.addAll(districtIds);
            ids.addAll(streetIds);
            ids.addAll(communityIds);
            //如果里面有数据那么就存储到Map集合中
            if (!ids.isEmpty()) {
                Map<Long, String> map = areaRepository.findByIdIn(ids).stream().collect(Collectors.toMap(Area::getId, Area::getName));
                if (!map.isEmpty()) {
                    //遍历存储数据
                    for (HouseVO vo : houseList) {
                        vo.setProvince(map.get(vo.getProvinceId()));
                        vo.setCity(map.get(vo.getCityId()));
                        vo.setDistrict(map.get(vo.getDistrictId()));
                        vo.setStreet(map.get(vo.getStreetId()));
                        vo.setCommunity(map.get(vo.getCommunityId()));
                    }
                }
            }
            // 查询小区名称
            List<String> rcIds = houseList.stream().filter(vo -> vo.getResidenceCommunityId() != null).map(HouseVO::getResidenceCommunityId).distinct().collect(Collectors.toList());
            //如果里面有数据那么就存储到Map集合中
            if (!rcIds.isEmpty()) {
                Map<String, String> map = communityRepository.findByIdIn(rcIds).stream().collect(Collectors.toMap(Community::getId, Community::getName));
                //遍历存储数据
                if (!map.isEmpty()) {
                    houseList.forEach(vo -> vo.setResidenceCommunityName(map.get(vo.getResidenceCommunityId())));
                }
            }
            // 查询楼栋名称
            List<Long> buildingIds = houseList.stream().filter(vo -> vo.getBuildingNum() != null).map(HouseVO::getBuildingNum).distinct().collect(Collectors.toList());
            //如果里面有数据那么就存储到Map集合中
            if (!buildingIds.isEmpty()) {
                Map<Long, String> map = communityBuildRepository.findByIdIn(buildingIds).stream().filter(vo -> vo.getName() != null).collect(Collectors.toMap(CommunityBuild::getId, CommunityBuild::getName));
                Map<Long, Integer> map2 = communityBuildRepository.findByIdIn(buildingIds).stream().filter(vo -> vo.getHouseNumber() != null).collect(Collectors.toMap(CommunityBuild::getId, CommunityBuild::getHouseNumber));
                Map<Long, Integer> map3 = communityBuildRepository.findByIdIn(buildingIds).stream().filter(vo -> vo.getType() != null).collect(Collectors.toMap(CommunityBuild::getId, CommunityBuild::getType));
                //遍历存储数据
                if (!map.isEmpty()) {
                    houseList.forEach(vo -> vo.setBuildingNumName(map.get(vo.getBuildingNum())));
                }
                //遍历存储数据
                if (!map2.isEmpty()) {
                    houseList.forEach(vo -> vo.setHouseNumber(map2.get(vo.getBuildingNum())));
                }
                //遍历存储数据
                if (!map3.isEmpty()) {
                    houseList.forEach(vo -> vo.setBuildType(map3.get(vo.getBuildingNum())));
                }
            }
            // 查询单元名称
            List<Long> unitIds = houseList.stream().filter(vo -> vo.getUnit() != null).map(HouseVO::getUnit).distinct().collect(Collectors.toList());
            //如果里面有数据那么就存储到Map集合中
            if (!unitIds.isEmpty()) {
                Map<Long, String> map = communityBuildUnitRepository.findByIdIn(unitIds).stream().collect(Collectors.toMap(CommunityBuildUnit::getId, CommunityBuildUnit::getName));
                //遍历存储数据
                if (!map.isEmpty()) {
                    houseList.forEach(vo -> vo.setUnitName(map.get(vo.getUnit())));
                }
            }
            // 查询所属物业名称
            List<Long> propertyIds = houseList.stream().filter(vo -> vo.getHousePropertyId() != null).map(HouseVO::getHousePropertyId).distinct().collect(Collectors.toList());
            //如果里面有数据那么就存储到Map集合中
            if (!propertyIds.isEmpty()) {
                Map<Long, String> map = departmentRepository.findByIdIn(propertyIds).stream().collect(Collectors.toMap(Department::getId, Department::getName));
                //遍历存储数据
                if (!map.isEmpty()) {
                    houseList.forEach(vo -> vo.setHouseProperty(map.get(vo.getHousePropertyId())));
                }
            }
            // 查询产权单位名称
            List<Long> ownerIds = houseList.stream().filter(vo -> vo.getOrgId() != null).map(HouseVO::getOrgId).distinct().collect(Collectors.toList());
            //如果里面有数据那么就存储到Map集合中
            if (!ownerIds.isEmpty()) {
                Map<Long, String> map = departmentRepository.findByIdIn(ownerIds).stream().collect(Collectors.toMap(Department::getId, Department::getName));
                //遍历存储数据
                if (!map.isEmpty()) {
                    houseList.forEach(vo -> vo.setOrgName(map.get(vo.getOrgId())));
                }
            }
            //查询承租人名称
            List<String> houseIds = houseList.stream().map(HouseVO::getId).distinct().collect(Collectors.toList());
            //如果里面有数据那么就存储到Map集合中
            if (!houseIds.isEmpty()) {
                String sql = "SELECT m.house_id as houseId,u.name AS name1,u2.name AS name2 \n" +
                        "FROM saas_house_match_user m \n" +
                        "LEFT JOIN `saas_app_user` u ON m.match_user_id = u.id\n" +
                        "LEFT JOIN `saas_app_user` u2 ON m.match_user_id = u2.enterprise_user_id  \n" +
                        "WHERE m.status = 1 and m.house_id in " + SqlUtils.foreachIn(houseIds.size());
                List<MatchUserVO> matchUsers = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(MatchUserVO.class), houseIds.toArray());
                //如果里面有数据那么就存储到Map集合中
                if (!matchUsers.isEmpty()) {
                    //遍历存储数据
                    for (HouseVO vo : houseList) {
                        List<String> list = new ArrayList<>();
                        for (MatchUserVO matchUser : matchUsers) {
                            if (vo.getId().equals(matchUser.getHouseId())) {
                                String name = "";
                                String name1 = matchUser.getName1();
                                String name2 = matchUser.getName2();
                                if (StringUtils.isNotBlank(name1)) {
                                    name = name1;
                                }
                                if (StringUtils.isNotBlank(name2)) {
                                    name = name2;
                                }
                                list.add(name);
                            }
                        }
                        vo.setMatchUser(list);
                    }
                }
            }
            //把查询到的数据存储到apiData中
            apiData.setData(houseList);
            apiData.setTotal(total);
            apiData.setIsMatchTotal(isMatchTotal);
            apiData.setUnMatchTotal(unMatchTotal);
            apiData.setOrgIds(orgIds);
            resultBean.setResultData(apiData);
        } catch (Exception e) {
            log.error("HouseService houseList Exception = {}" + e.getMessage(), e);
            resultBean.setCode("10001");
            resultBean.setMsg("10001");
        }
        //把数据输出给前端页面
        resultBean.setResultData(apiData);
        return resultBean;
    }

    public ResultBean<List<OrgVO>> findOrgId(String accessToken, List<Long> orgIds) {
        try {
            List<OrgVO> list = new ArrayList<>();

            if (orgIds != null && !orgIds.isEmpty()) {
                List<Department> departments = departmentRepository.findByIdIn(orgIds);
                for (Department department : departments) {
                    OrgVO vo = new OrgVO();
                    vo.setOrgId(department.getId());
                    vo.setOrgName(department.getName());
                    list.add(vo);
                }
            }

            return ResultBean.successfulResult(list);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }
    }

    public ResultBean<OrgVO> findUserOrg(String accessToken) {
        try {
            TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
            if (tokenRedisVo == null) {
                return ResultBean.failedResultOfToken();
            }
            Long enterpriseId = tokenRedisVo.getEnterpriseId();
            DepartmentHierarchy hierarchy = departmentHierarchyRepository.findByOrgIdAndStatus(enterpriseId, 1);
            if (hierarchy == null) {
                return ResultBean.failedResultWithMsg("用户所属机构未设置层级架构");
            }
            Long id = hierarchy.getId();
            List<DepartmentEnterpriseTagRef> refs = departmentEnterpriseTagRefRepository.findByDepRefId(id);
            List<Long> enterpriseTagIds = refs.stream().map(DepartmentEnterpriseTagRef::getTagId).distinct().collect(Collectors.toList());//用户企业标签

            Optional<Department> departmentOptional = departmentRepository.findById(enterpriseId);
            if (!departmentOptional.isPresent()) {
                return ResultBean.failedResultWithMsg("用户所属机构不存在");
            }
            Department department = departmentOptional.get();

            OrgVO vo = new OrgVO();
            if (enterpriseTagIds.contains(2L)) {//产权单位
                vo.setOrgId(department.getId());
                vo.setOrgName(department.getName());
            }

            return ResultBean.successfulResult(vo);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }
    }

    public ResultBean<Map<String, Object>> findOwner(String houseId) {
        try {
            if (houseId == null) {
                return ResultBean.failedResultOfParamWithMsg("公租房ID为空");
            }

            Optional<House> optional = houseRepository.findById(houseId);
            House house = optional.get();

            Optional<Department> departmentOptional = departmentRepository.findById(house.getOrgId());
            if (!departmentOptional.isPresent()) {
                return ResultBean.failedResultWithMsg("产权人不存在");
            }
            Department department = departmentOptional.get();

            Map<String, Object> map = new HashMap<>();
            map.put("orgName", department.getName());

            return ResultBean.successfulResult(map);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }
    }

    /**
     * 产权人列表,实现了查询出所有产权人所有人数据
     * @param accessToken 权限鉴定
     * @param name 用户名字
     * @return
     */
    public ResultBean<List<OrgVO>> ownerList(String accessToken, String name) {
        try {
            //获取用户登录权限
            TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
            //用户权限的鉴定
            if (tokenRedisVo == null) {
                return ResultBean.failedResultOfToken();
            }
            //获取企业ID
            Long enterpriseId = tokenRedisVo.getEnterpriseId();
            //判断是否是企业用户
            if (enterpriseId == null) {
                return ResultBean.failedResultWithMsg("非企业用户");
            }
            //根据企业ID查询出对应机构
            Optional<Department> optional = departmentRepository.findById(enterpriseId);
            //如果不存在
            if (!optional.isPresent()) {
                return ResultBean.failedResultWithMsg("用户所属机构不存在");
            }
            //获取机构数据
            Department department = optional.get();
            //获取省份ID
            Long provinceId = department.getProvinceId();//省id
            //根据省份ID,产权ID,查询出对应的机构
            String sql = "SELECT h.`org_id` AS orgId,h.`name` AS orgName FROM `saas_department_hierarchy` h LEFT JOIN `saas_department_enterprise_tag_ref` r ON h.id = r.dep_ref_id  \n" +
                    "WHERE h.`org_id` IN (SELECT d.id FROM `saas_department` d WHERE d.`province_id` = ? AND d.`parent_id` = 0) \n" +
                    "AND r.`tag_id` = 2";
            //创建一个字符串拼接变量
            StringBuilder where = new StringBuilder();
            List<Object> args = new ArrayList<>();
            args.add(provinceId);
            //如果用传进来对应的机构名字就查询出对应机构的数据
            if (!StringUtils.isBlank(name)) {
                where.append(" AND h.name LIKE '%" + name + "%' ");
            }
            //查询出对应机构的数据
            List<OrgVO> list = jdbcTemplate.query(sql + where, new BeanPropertyRowMapper<>(OrgVO.class), args.toArray());
            //把数据返回给前端页面
            return ResultBean.successfulResult(list);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }
    }
    /**
     * 所属物业列表,实现了查询出所有物业列表所有人数据
     * @param accessToken 权限鉴定
     * @param name 用户名字
     * @return
     */
    public ResultBean<List<OrgVO>> propertyList(String accessToken, String name) {
        try {
            //获取用户登录权限
            TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
            //用户权限的鉴定
            if (tokenRedisVo == null) {
                return ResultBean.failedResultOfToken();
            }
            //获取企业ID
            Long enterpriseId = tokenRedisVo.getEnterpriseId();
            //判断是否是企业用户
            if (enterpriseId == null) {
                return ResultBean.failedResultWithMsg("非企业用户");
            }
            //根据企业ID查询出对应物业
            Optional<Department> optional = departmentRepository.findById(enterpriseId);
            if (!optional.isPresent()) {
                //如果不存在
                return ResultBean.failedResultWithMsg("用户所属机构不存在");
            }
            //获取物业数据
            Department department = optional.get();
            //获取省份ID
            Long provinceId = department.getProvinceId();//省id
            //根据省份ID,产权ID,查询出对应的物业
            String sql = "SELECT h.`org_id` AS orgId,h.`name` AS orgName FROM `saas_department_hierarchy` h LEFT JOIN `saas_department_enterprise_tag_ref` r ON h.id = r.dep_ref_id  \n" +
                    "WHERE h.`org_id` IN (SELECT d.id FROM `saas_department` d WHERE d.`province_id` = ? AND d.`parent_id` = 0) \n" +
                    "AND r.`tag_id` = 4";
            //创建一个字符串拼接变量
            StringBuilder where = new StringBuilder();
            List<Object> args = new ArrayList<>();
            args.add(provinceId);
            //如果用传进来对应的机构名字就查询出对应机构的数据
            if (!StringUtils.isBlank(name)) {
                where.append(" AND h.name LIKE '%" + name + "%' ");
            }
            //查询出对应物业的数据
            List<OrgVO> list = jdbcTemplate.query(sql + where, new BeanPropertyRowMapper<>(OrgVO.class), args.toArray());
            //把数据返回给前端页面
            return ResultBean.successfulResult(list);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }
    }
    /**
     * 绑定产权所有人,实现绑定产权所有人
     * @param accessToken 权限鉴定
     * @param houseIds 公租房ID
     * @param orgId 产权人ID
     * @return
     */
    public ResultBean<Object> bindOwner(String accessToken, List<String> houseIds, Long orgId) {
        try {
            //获取用户登录权限
            TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
            //用户权限的鉴定
            if (tokenRedisVo == null) {
                return ResultBean.failedResultOfToken();
            }
            //获取用户ID
            String userId = tokenRedisVo.getId();
            //获取企业ID
            Long enterpriseId = tokenRedisVo.getEnterpriseId();
            //判断是否是企业用户
            if (enterpriseId == null) {
                return ResultBean.failedResultWithMsg("非企业用户");
            }
            //根据企业ID查询出对应机构
            Optional<Department> departmentOptional = departmentRepository.findById(enterpriseId);
            //如果不存在
            if (!departmentOptional.isPresent()) {
                return ResultBean.failedResultWithMsg("用户所属机构不存在");
            }
            //获取机构数据
            Department department = departmentOptional.get();
            //如果公租房id不存在
            if (houseIds == null) {
                return ResultBean.failedResultOfParamWithMsg("公租房id为空");
            }
            //如果产权所有人id不存在
            if (orgId == null) {
                return ResultBean.failedResultOfParamWithMsg("产权所有人id为空");
            }

            Date date = new Date();
            List<House> list = houseRepository.findByIdIn(houseIds);
            //批量修改
            for (House house : list) {
                //绑定审批状态
                Map<String, String> map = findEnterpriseAuditStatus(house.getProvinceId(), house.getCityId(), house.getDistrictId(), house.getStreetId(), house.getCommunityId());
                String status = map.get("status");

                house.setOrgId(orgId);
                house.setBindStatus(HouseBindStatusEnum.BIND.getValue());
                if (status.equals("0")) {//不需要审批
                    house.setAuditStatus(HouseAuditStatusEnum.AUDIT.getValue());
                } else {//需要审批
                    house.setAuditStatus(HouseAuditStatusEnum.NO_AUDIT.getValue());
                    //生成代办事项
                    HouseInfoVO data = findHouse(house.getId()).getResultData();
                    BacklogItem backlogItem = new BacklogItem();
                    backlogItem.setHouseId(house.getId());
                    String auditOrgId = map.get("orgId");
                    backlogItem.setOrgId(Long.parseLong(auditOrgId));
                    backlogItem.setUserOrgId(enterpriseId);
                    backlogItem.setType(BacklogItemTypeEnum.HOUSE_SAVE.getValue());
                    //backlogItem.setTitle(department.getName() + "提交的绑定公租房申请");
                    backlogItem.setTitle(data.getResidenceCommunityName() + data.getBuildingNumName() + "栋" +
                            data.getUnitName() + "单元" + data.getRoomNumber() + "号" + "房屋绑定公租房申请");
                    backlogItem.setBusinessStatus("待审批");
                    backlogItem.setHandleStatus(0);//0未读/未处理
                    backlogItem.setOperatorId(userId);
                    backlogItem.setCreateTime(date);
                    backlogItem.setAuditStatus(0);
                    backlogItemRepository.save(backlogItem);
                }
                //保存数据
                houseRepository.save(house);
            }
            //把成功结果传给前端页面
            return ResultBean.successfulResult(null);
        } catch (Exception e) {
            //打印报错日志
            log.error("HouseService bindOwner Exception = {}" + e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }
    }

    @Transactional
    public ResultBean<Object> updateBindOwner(String accessToken, String houseId, Long orgId) {
        try {
            TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
            if (tokenRedisVo == null) {
                return ResultBean.failedResultOfToken();
            }
            String userId = tokenRedisVo.getId();
            Long enterpriseId = tokenRedisVo.getEnterpriseId();
            if (enterpriseId == null) {
                return ResultBean.failedResultWithMsg("非企业用户");
            }

            Optional<Department> departmentOptional = departmentRepository.findById(enterpriseId);
            if (!departmentOptional.isPresent()) {
                return ResultBean.failedResultWithMsg("用户所属机构不存在");
            }
            Department department = departmentOptional.get();

            if (StringUtils.isBlank(houseId)) {
                return ResultBean.failedResultOfParamWithMsg("公租房id为空");
            }
            if (orgId == null) {
                return ResultBean.failedResultOfParamWithMsg("产权所有人id为空");
            }

            Optional<House> houseOpt = houseRepository.findById(houseId);
            if (!houseOpt.isPresent()) {
                return ResultBean.failedResultWithMsg("公租房不存在");
            }
            House house = houseOpt.get();
            Date date = new Date();

            //删除未审核待办事项
            jdbcTemplate.update("DELETE from saas_backlog_item WHERE house_id = ? AND type = 1 AND handle_status = 0", houseId);
            //绑定审批状态
            Map<String, String> map = findEnterpriseAuditStatus(house.getProvinceId(), house.getCityId(), house.getDistrictId(), house.getStreetId(), house.getCommunityId());
            String status = map.get("status");

            house.setOrgId(orgId);
            house.setBindStatus(HouseBindStatusEnum.BIND.getValue());
            if (status.equals("0")) {//不需要审批
                house.setAuditStatus(HouseAuditStatusEnum.AUDIT.getValue());
            } else {//需要审批
                house.setAuditStatus(HouseAuditStatusEnum.NO_AUDIT.getValue());
                //生成代办事项
                HouseInfoVO data = findHouse(house.getId()).getResultData();
                BacklogItem backlogItem = new BacklogItem();
                backlogItem.setHouseId(house.getId());
                String auditOrgId = map.get("orgId");
                backlogItem.setOrgId(Long.parseLong(auditOrgId));
                backlogItem.setUserOrgId(enterpriseId);
                backlogItem.setType(BacklogItemTypeEnum.HOUSE_SAVE.getValue());
                //backlogItem.setTitle(department.getName() + "提交的绑定公租房申请");
                backlogItem.setTitle(data.getResidenceCommunityName() + data.getBuildingNumName() + "栋" +
                        data.getUnitName() + "单元" + data.getRoomNumber() + "号" + "房屋绑定公租房申请");
                backlogItem.setBusinessStatus("待审批");
                backlogItem.setHandleStatus(0);//0未读/未处理
                backlogItem.setOperatorId(userId);
                backlogItem.setCreateTime(date);
                backlogItem.setAuditStatus(0);
                backlogItemRepository.save(backlogItem);
            }
            houseRepository.save(house);

            return ResultBean.successfulResult(null);
        } catch (Exception e) {
            log.error("HouseService updateBindOwner Exception = {}" + e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }
    }
    /**
     *  解绑产权所有人，实现了产权所有人的解绑
     * @param houseId 公租房ID
     * @return
     */
    public ResultBean<Object> unBindOwner(String houseId) {
        try {
            //判断是否有公租房ID
            if (StringUtils.isBlank(houseId)) {
                return ResultBean.failedResultOfParamWithMsg("公租房id为空");
            }
            //通过公租房ID,查询公租房数据
            Optional<House> optional = houseRepository.findById(houseId);
            if (!optional.isPresent()) {
                return ResultBean.failedResultWithMsg("公租房不存在");
            }
            //获取公租房数据
            House house = optional.get();
            //修改公租房绑定，取消绑定
            house.setOrgId(null);
            house.setBindStatus(HouseBindStatusEnum.UNBIND.getValue());
            house.setAuditStatus(HouseAuditStatusEnum.NO_AUDIT.getValue());
            //修改数据
            houseRepository.save(house);
            //把修改后的数据返回给前端页面
            return ResultBean.successfulResult(null);
        } catch (Exception e) {
            //记录报错日志
            log.error("HouseService unBindOwner Exception = {}" + e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }
    }
    /**
     * 新增公租房,实现了公租房房源的录入
     * @param accessToken 权限鉴定
     * @param vo 一个用来接收前端传过来的数据的实体类
     * @return
     */
    public ResultBean<Object> saveHouse(String accessToken, HouseVO vo) {
        try {
            //获取用户登录权限
            TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
            //用户权限的鉴定
            if (tokenRedisVo == null) {
                return ResultBean.failedResultOfToken();
            }
            //获取用户ID
            String userId = tokenRedisVo.getId();
            //获取企业ID
            Long enterpriseId = tokenRedisVo.getEnterpriseId();
            //判断是否是企业用户
            if (enterpriseId == null) {
                return ResultBean.failedResultWithMsg("非企业用户");
            }
            //根据企业ID查询出对应机构
            Optional<Department> departmentOptional = departmentRepository.findById(enterpriseId);
            if (!departmentOptional.isPresent()) {
                return ResultBean.failedResultWithMsg("用户所属机构不存在");
            }
            //获取机构数据
            Department department = departmentOptional.get();

            // 参数验证
            //这里设置了前端传过来的参数不能为空,如果为空,那么就会给出对应的提示
            if (vo == null) {
                return ResultBean.failedResultOfParamWithMsg("参数为空");
            }
            if (vo.getProvinceId() == null || vo.getCityId() == null || vo.getDistrictId() == null || vo.getStreetId() == null || vo.getCommunityId() == null) {
                return ResultBean.failedResultOfParamWithMsg("请输入区域");
            }
            if (StringUtils.isBlank(vo.getAddress())) {
                return ResultBean.failedResultOfParamWithMsg("请输入地址");
            }
            if (vo.getResidenceCommunityId() == null) {
                return ResultBean.failedResultOfParamWithMsg("请输入小区名称");
            }
            if (vo.getBuildingNum() == null) {
                return ResultBean.failedResultOfParamWithMsg("请输入楼栋");
            }
            if (vo.getUnit() == null) {
                return ResultBean.failedResultOfParamWithMsg("请输入单元");
            }
            if (StringUtils.isBlank(vo.getRoomNumber())) {
                return ResultBean.failedResultOfParamWithMsg("请输入房号");
            }
            if (vo.getFloor() == null) {
                return ResultBean.failedResultOfParamWithMsg("请输入楼层");
            }
            if (vo.getArea() == null) {
                return ResultBean.failedResultOfParamWithMsg("请输入面积");
            }
            if (vo.getRoom() == null || vo.getHall() == null || vo.getToilet() == null) {
                return ResultBean.failedResultOfParamWithMsg("请输入户型");
            }
            if (vo.getHouseNature() == null) {
                return ResultBean.failedResultOfParamWithMsg("请输入性质");
            }
            if (vo.getHousePropertyId() == null) {
                return ResultBean.failedResultOfParamWithMsg("请输入所属物业");
            }

            Date date = new Date();

            //去重,根据单元号和房间号判断输入的信息是否重复,如果重复不给予录入
            List<House> list = houseRepository.findByUnitAndRoomNumber(vo.getUnit(), vo.getRoomNumber());
            if (list.size() > 0) {
                return ResultBean.failedResultOfParamWithMsg("同一个小区中，不能出现同一个楼宇单元的房产");
            }

            //绑定审批状态
            Map<String, String> map = findEnterpriseAuditStatus(vo.getProvinceId(), vo.getCityId(), vo.getDistrictId(), vo.getStreetId(), vo.getCommunityId());
            String status = map.get("status");
            //根据录入的信息存储公租房
            House house = new House();
            //根据录入的省份获取省份名字
            Area area = areaRepository.findByIdAndLevel(vo.getProvinceId(), 1);
            String province = pinyin(area.getName());//省份3个首字母
            //生产一个公租房ID
            // redisTemplate.opsForValue().increment("HOUSE_ID:" + province, 1);
            // Object id = redisTemplate.opsForValue().get("HOUSE_ID:" + province);//获取redis自增id
            //
            // int len = 10 - id.toString().length();
            // StringBuilder builder = new StringBuilder();
            // for (int i = 0; i < len; i++) {//拼接成10位字符串
            //     builder.append("0");
            // }
            //拼接公租房ID
            String newId = province + System.currentTimeMillis() + RandomUtils.nextInt(1000, 10000);//省份3位首字母 + 10数字
            house.setId(newId);
            //获取产权人ID
            Long orgId = vo.getOrgId();
            //绑定产权单位
            if (orgId != null) {
                house.setOrgId(orgId);
                house.setBindStatus(HouseBindStatusEnum.BIND.getValue());
                if (status.equals("0")) {//不需要审批
                    house.setAuditStatus(HouseAuditStatusEnum.AUDIT.getValue());
                } else {//需要审批
                    house.setAuditStatus(HouseAuditStatusEnum.NO_AUDIT.getValue());
                }
            } else {
                //产权绑定
                house.setBindStatus(HouseBindStatusEnum.UNBIND.getValue());
                //产权未审核
                house.setAuditStatus(HouseAuditStatusEnum.NO_AUDIT.getValue());
            }
            //根据录入的信息存储公租房
            house.setProvinceId(vo.getProvinceId());
            house.setCityId(vo.getCityId());
            house.setDistrictId(vo.getDistrictId());
            house.setStreetId(vo.getStreetId());
            house.setCommunityId(vo.getCommunityId());
            house.setAddress(vo.getAddress());
            house.setResidenceCommunityId(vo.getResidenceCommunityId());
            //根据输入的小区ID查询出对应的小区
            Optional<Community> communityOpt = communityRepository.findById(vo.getResidenceCommunityId());
            if (!communityOpt.isPresent()) {
                return ResultBean.failedResultWithMsg("小区不存在");
            }
            //根据录入的信息存储公租房
            house.setResidenceCommunityName(communityOpt.get().getName());
            house.setBuildingNum(vo.getBuildingNum());
            //根据输入的楼栋查询出对应的楼栋
            Optional<CommunityBuild> buildOptional = communityBuildRepository.findById(vo.getBuildingNum());
            if (!buildOptional.isPresent()) {
                return ResultBean.failedResultWithMsg("楼栋不存在");
            }
            house.setUnit(vo.getUnit());
            //根据输入的单元查询出对应的单元
            Optional<CommunityBuildUnit> unitOptional = communityBuildUnitRepository.findById(vo.getUnit());
            if (!unitOptional.isPresent()) {
                return ResultBean.failedResultWithMsg("单元不存在");
            }
            //根据录入的信息存储公租房,和设置一些默认值
            house.setRoomNumber(vo.getRoomNumber());
            house.setFloor(vo.getFloor());
            house.setArea(vo.getArea());
            house.setRoom(vo.getRoom());
            house.setHall(vo.getHall());
            house.setToilet(vo.getToilet());
            house.setHouseNature(vo.getHouseNature());
            house.setHousePropertyId(vo.getHousePropertyId());
            house.setMatchStatus(HouseMatchStatusEnum.NOT_RENT.getValue());
            house.setLeaseStatus(0);
            house.setContractStatus(0);
            house.setRecordStatus(0);
            house.setPwdLockStatus(0);
            house.setCheckStatus(0);
            house.setRecordChangeStatus(0);
            house.setOperationId(userId);
            house.setCreateTime(date);
            House save = houseRepository.save(house);

            //生成待办事项
            if (orgId != null && status.equals("1")) {
                String auditOrgId = map.get("orgId");
                BacklogItem backlogItem = new BacklogItem();
                backlogItem.setHouseId(save.getId());
                backlogItem.setOrgId(Long.parseLong(auditOrgId));
                backlogItem.setUserOrgId(enterpriseId);
                backlogItem.setType(BacklogItemTypeEnum.HOUSE_SAVE.getValue());
                //backlogItem.setTitle(department.getName() + "提交的绑定公租房申请");
                backlogItem.setTitle(communityOpt.get().getName() + buildOptional.get().getName() + "栋" +
                        unitOptional.get().getName() + "单元" + vo.getRoomNumber() + "号" + "房屋绑定公租房申请");
                backlogItem.setBusinessStatus("待审批");
                backlogItem.setHandleStatus(0);//0未读/未处理
                backlogItem.setOperatorId(userId);
                backlogItem.setCreateTime(date);
                backlogItem.setAuditStatus(0);
                backlogItemRepository.save(backlogItem);
            }
            //提示前端录入成功
            return ResultBean.successfulResult(null);
        } catch (Exception e) {
            //如果有异常记录异常日志
            log.error("HouseService saveHouse Exception = {}" + e.getMessage(), e);
            // 出现异常告诉用户,系统异常，请稍后再试
            return ResultBean.failedResultOfException();
        }
    }
    /**
     * 修改公租房,实现了未绑定公租房的修改
     * @param accessToken 权限鉴定
     * @param vo 公租房实体类
     * @return
     */
    public ResultBean<Object> updateHouse(String accessToken, HouseVO vo) {
        try {
            //获取用户登录权限
            TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
            //用户权限的鉴定
            if (tokenRedisVo == null) {
                return ResultBean.failedResultOfToken();
            }
            //获取用户ID
            String userId = tokenRedisVo.getId();
            //获取企业ID
            Long enterpriseId = tokenRedisVo.getEnterpriseId();
            if (enterpriseId == null) {
                return ResultBean.failedResultWithMsg("非企业用户");
            }
            //根据用户ID查询出用户层级权限
            Optional<Department> departmentOptional = departmentRepository.findById(enterpriseId);
            if (!departmentOptional.isPresent()) {
                return ResultBean.failedResultWithMsg("用户所属机构不存在");
            }
            Department department = departmentOptional.get();

            // 参数验证，根据前端传来的参数进行对应的数据修改
            if (vo == null) {
                return ResultBean.failedResultOfParamWithMsg("参数为空");
            }
            if (vo.getProvinceId() == null || vo.getCityId() == null || vo.getDistrictId() == null || vo.getStreetId() == null || vo.getCommunityId() == null) {
                return ResultBean.failedResultOfParamWithMsg("请输入区域");
            }
            if (StringUtils.isBlank(vo.getAddress())) {
                return ResultBean.failedResultOfParamWithMsg("请输入地址");
            }
            if (vo.getResidenceCommunityId() == null) {
                return ResultBean.failedResultOfParamWithMsg("请输入小区名称");
            }
            if (vo.getBuildingNum() == null) {
                return ResultBean.failedResultOfParamWithMsg("请输入楼栋");
            }
            if (vo.getUnit() == null) {
                return ResultBean.failedResultOfParamWithMsg("请输入单元");
            }
            if (StringUtils.isBlank(vo.getRoomNumber())) {
                return ResultBean.failedResultOfParamWithMsg("请输入房号");
            }
            if (vo.getFloor() == null) {
                return ResultBean.failedResultOfParamWithMsg("请输入楼层");
            }
            if (vo.getArea() == null) {
                return ResultBean.failedResultOfParamWithMsg("请输入面积");
            }
            if (vo.getRoom() == null || vo.getHall() == null || vo.getToilet() == null) {
                return ResultBean.failedResultOfParamWithMsg("请输入户型");
            }
            if (vo.getHouseNature() == null) {
                return ResultBean.failedResultOfParamWithMsg("请输入性质");
            }
            if (vo.getHousePropertyId() == null) {
                return ResultBean.failedResultOfParamWithMsg("请输入所属物业");
            }
            //存储公租房数据
            Date date = new Date();
            //通过公租房ID获取数据
            Optional<House> houseOpt = houseRepository.findById(vo.getId());
            if (!houseOpt.isPresent()) {
                return ResultBean.failedResultWithMsg("公租房不存在");
            }
            //获取公租房数据，公租房ID，机构ID
            House house = houseOpt.get();
            String houseId = house.getId();
            Long orgId = vo.getOrgId();

            //去重，通过单元和房间号去重，同一个小区中，不能出现相同的房产
            if ((!house.getRoomNumber().equals(vo.getRoomNumber())) || house.getUnit().longValue() != vo.getUnit()) {
                List<House> list = houseRepository.findByUnitAndRoomNumber(vo.getUnit(), vo.getRoomNumber());
                if (list.size() > 0) {
                    return ResultBean.failedResultOfParamWithMsg("同一个小区中，不能出现相同的房产");
                }
            }
            //设置前端传过来的数据
            house.setProvinceId(vo.getProvinceId());
            house.setCityId(vo.getCityId());
            house.setDistrictId(vo.getDistrictId());
            house.setStreetId(vo.getStreetId());
            house.setCommunityId(vo.getCommunityId());
            house.setAddress(vo.getAddress());
            house.setResidenceCommunityId(vo.getResidenceCommunityId());
            //根据小区ID查询数据
            Optional<Community> communityOpt = communityRepository.findById(vo.getResidenceCommunityId());
            if (!communityOpt.isPresent()) {
                return ResultBean.failedResultWithMsg("小区不存在");
            }
            //设置小区名字
            house.setResidenceCommunityName(communityOpt.get().getName());
            house.setBuildingNum(vo.getBuildingNum());
            //根据楼栋数，获取楼栋
            Optional<CommunityBuild> buildOptional = communityBuildRepository.findById(vo.getBuildingNum());
            if (!buildOptional.isPresent()) {
                return ResultBean.failedResultWithMsg("楼栋不存在");
            }
            house.setUnit(vo.getUnit());
            //根据单元获取数据
            Optional<CommunityBuildUnit> unitOptional = communityBuildUnitRepository.findById(vo.getUnit());
            if (!unitOptional.isPresent()) {
                return ResultBean.failedResultWithMsg("单元不存在");
            }
            //设置公租房数据
            house.setRoomNumber(vo.getRoomNumber());
            house.setFloor(vo.getFloor());
            house.setArea(vo.getArea());
            house.setRoom(vo.getRoom());
            house.setHall(vo.getHall());
            house.setToilet(vo.getToilet());
            house.setHouseNature(vo.getHouseNature());
            house.setHousePropertyId(vo.getHousePropertyId());
            house.setOperationId(userId);
            //修改，保存
            houseRepository.save(house);

            //绑定产权单位
            if (orgId != null) {
                //删除未审核待办事项
                jdbcTemplate.update("DELETE from saas_backlog_item WHERE house_id = ? AND type = 1 AND handle_status = 0", houseId);
                //绑定审批状态
                Map<String, String> map = findEnterpriseAuditStatus(vo.getProvinceId(), vo.getCityId(), vo.getDistrictId(), vo.getStreetId(), vo.getCommunityId());
                String status = map.get("status");

                house.setOrgId(orgId);
                house.setBindStatus(HouseBindStatusEnum.BIND.getValue());
                if (status.equals("0")) {//不需要审批
                    house.setAuditStatus(HouseAuditStatusEnum.AUDIT.getValue());
                } else {//需要审批
                    house.setAuditStatus(HouseAuditStatusEnum.NO_AUDIT.getValue());
                    //生成待办事项
                    String auditOrgId = map.get("orgId");
                    BacklogItem backlogItem = new BacklogItem();
                    backlogItem.setHouseId(houseId);
                    backlogItem.setOrgId(Long.parseLong(auditOrgId));
                    backlogItem.setUserOrgId(enterpriseId);
                    backlogItem.setType(BacklogItemTypeEnum.HOUSE_SAVE.getValue());
                    //backlogItem.setTitle(department.getName() + "提交的绑定公租房申请");
                    backlogItem.setTitle(communityOpt.get().getName() + buildOptional.get().getName() + "栋" +
                            unitOptional.get().getName() + "单元" + vo.getRoomNumber() + "号" + "房屋绑定公租房申请");
                    backlogItem.setBusinessStatus("待审批");
                    backlogItem.setHandleStatus(0);//0未读/未处理
                    backlogItem.setOperatorId(userId);
                    backlogItem.setCreateTime(date);
                    backlogItem.setAuditStatus(0);
                    //修改，保存
                    backlogItemRepository.save(backlogItem);
                }
            } else {
                house.setBindStatus(HouseBindStatusEnum.UNBIND.getValue());
                house.setAuditStatus(HouseAuditStatusEnum.NO_AUDIT.getValue());
            }
            //把修改后的数据返回给前端页面
            return ResultBean.successfulResult(null);
        } catch (Exception e) {
            //记录报错日志
            log.error("HouseService updateHouse Exception = {}" + e.getMessage(), e);
            //报错，返回给前端页面系统异常，请稍后再试
            return ResultBean.failedResultOfException();
        }
    }

    /**
     * 获取省份3个首字母
     *
     * @param name
     * @return
     */
    public String pinyin(String name) {
        StringBuffer pybf = new StringBuffer();
        char[] arr = name.toCharArray();
        HanyuPinyinOutputFormat defaultFormat = new HanyuPinyinOutputFormat();
        defaultFormat.setCaseType(HanyuPinyinCaseType.UPPERCASE);
        defaultFormat.setToneType(HanyuPinyinToneType.WITHOUT_TONE);
        for (int i = 0; i < arr.length; i++) {
            if (Character.toString(arr[i]).matches("[\\u4E00-\\u9FA5]+")) {
                try {
                    String[] temp = PinyinHelper.toHanyuPinyinStringArray(arr[i], defaultFormat);
                    if (temp != null) {
                        pybf.append(temp[0].charAt(0));
                    }
                } catch (BadHanyuPinyinOutputFormatCombination e) {
                    e.printStackTrace();
                }
            } else {
                pybf.append(arr[i]);
            }
        }
        return pybf.toString().replaceAll("\\W", "").trim().substring(0, 3);
    }

    public ResultBean<HouseInfoVO> findHouse(String houseId) {
        try {
            if (StringUtils.isBlank(houseId)) {
                return ResultBean.failedResultOfParamWithMsg("公租房id为空");
            }
            Optional<House> optional = houseRepository.findById(houseId);
            if (!optional.isPresent()) {
                return ResultBean.failedResultWithMsg("公租房不存在");
            }

            String sql = "select h.id,h.province_id as provinceId,h.city_id as cityId,h.district_id as districtId,h.street_id as streetId, \n" +
                    "h.community_id  as communityId,h.address,h.residence_community_id as residenceCommunityId,h.building_num as buildingNum,h.unit, \n    " +
                    "h.room_number as roomNumber,h.floor,h.area,h.room,h.hall,h.toilet,h.house_nature as houseNature,h.house_property_id as housePropertyId,h.org_id as orgId, \n" +
                    "c.name as residenceCommunityName,b.name as buildingNumName,u.name as unitName,d1.name as orgName,d2.name as houseProperty " +
                    "from saas_house h \n" +
                    "LEFT JOIN `saas_community` c ON h.`residence_community_id` = c.`id`\n" +
                    "LEFT JOIN `saas_community_build` b ON h.`building_num` = b.`id`\n" +
                    "LEFT JOIN `saas_community_build_unit` u ON h.`unit` = u.id\n" +
                    "LEFT JOIN `saas_department` d1 ON h.`org_id` = d1.id\n " +
                    "LEFT JOIN `saas_department` d2 ON h.`house_property_id` = d2.id where h.id = ?";
            HouseInfoVO vo = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(HouseInfoVO.class), houseId);

            List<Long> areaIds = Arrays.asList(vo.getProvinceId(), vo.getCityId(), vo.getDistrictId(), vo.getStreetId(), vo.getCommunityId());
            List<Area> areas = areaRepository.findByIdIn(areaIds);
            for (Area area : areas) {
                long id = area.getId();
                String name = area.getName();
                if (vo.getProvinceId() == id) {
                    vo.setProvince(name);
                } else if (vo.getCityId() == id) {
                    vo.setCity(name);
                } else if (vo.getDistrictId() == id) {
                    vo.setDistrict(name);
                } else if (vo.getStreetId() == id) {
                    vo.setStreet(name);
                } else if (vo.getCommunityId() == id) {
                    vo.setCommunity(name);
                }
            }

            return ResultBean.successfulResult(vo);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }
    }
    /**
     * 删除公租房,实现了未审核的公租房的删除
     * @param houseId 公租房ID
     * @return
     */
    @Transactional
    public ResultBean<Object> deleteHouse(String houseId) {
        try {
            //判断是否有数据
            if (StringUtils.isBlank(houseId)) {
                return ResultBean.failedResultOfParamWithMsg("公租房id为空");
            }
            //根据公租房ID获取公租房数据
            Optional<House> optional = houseRepository.findById(houseId);
            if (!optional.isPresent()) {
                return ResultBean.failedResultWithMsg("公租房不存在");
            }

            House house = optional.get();

            //公租房：已审核的不能删除
            if (house.getAuditStatus() == 1) {
                return ResultBean.failedResultWithMsg("已审核的公租房不能删除");
            }
            //删除未审核的公租房数据
            houseRepository.delete(house);

            //删除绑定待办事项
            jdbcTemplate.update("DELETE from saas_backlog_item WHERE house_id = ? AND type = 1", houseId);
            //把结果返回给前端页面
            return ResultBean.successfulResult(null);
        } catch (Exception e) {
            //记录报错信息
            log.error(e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }
    }

    public ResultBean<Object> saveDecorate(String houseId, Integer decorateType) {
        try {
            if (StringUtils.isBlank(houseId)) {
                return ResultBean.failedResultOfParamWithMsg("公租房id为空");
            }
            if (decorateType == null) {
                return ResultBean.failedResultOfParamWithMsg("装修情况id为空");
            }

            Optional<House> optional = houseRepository.findById(houseId);
            if (!optional.isPresent()) {
                return ResultBean.failedResultWithMsg("公租房不存在");
            }

            House house = optional.get();
            house.setDecorateType(decorateType);//解绑产权所有人ID
            houseRepository.save(house);

            return ResultBean.successfulResult(null);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }
    }

    public ResultBean<Object> findFloor(Long buildId) {
        try {
            if (buildId == null) {
                return ResultBean.failedResultOfParamWithMsg("楼栋id为空");
            }

            Optional<CommunityBuild> optional = communityBuildRepository.findById(buildId);
            if (!optional.isPresent()) {
                return ResultBean.failedResultWithMsg("楼栋不存在");
            }
            CommunityBuild build = optional.get();
            Integer houseNumber = build.getHouseNumber();

            return ResultBean.successfulResult(houseNumber);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }
    }

    public ResultBean<List<Map<String, Object>>> communityList(Long communityId) {
        try {
            if (communityId == null) {
                return ResultBean.failedResultOfParamWithMsg("社区id为空");
            }

            List<Map<String, Object>> list = jdbcTemplate.queryForList("SELECT c.id AS communityId,c.name AS communityName,c.org_id as orgId,DATE_FORMAT(c.build_time,'%Y-%m-%d %H:%i:%s') as buildTime,c.address,c.property_company_name AS orgName FROM `saas_community` c where c.community_id = ? AND c.status = 1", communityId);

            return ResultBean.successfulResult(list);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }
    }

    /**
     * 公租房绑定产权单位审批验证
     *
     * @param provinceId  省
     * @param cityId      市
     * @param districtId  区/县
     * @param streetId    街道
     * @param communityId 社区
     * @return
     * @author shun
     * @date 2021年6月30日
     */
    public Map<String, String> findEnterpriseAuditStatus(Long provinceId, Long cityId, Long districtId, Long streetId,
                                                         Long communityId) {
        String sql = "SELECT h.org_id as orgId,h.area_id AS areaId,h.bind_auit_status AS bindAuitStatus "
                + "FROM saas_department_hierarchy h "
                + "LEFT JOIN saas_department d ON d.id = h.org_id "
                + "LEFT JOIN saas_department_enterprise_tag_ref f ON h.id = f.dep_ref_id "
                + "WHERE f.tag_id = 5 ";

        StringBuffer buf = new StringBuffer();
        if (provinceId != null) {
            buf.append(" AND (d.province_id=").append(provinceId);
        }

        if (cityId != null) {
            if (StringUtils.isEmpty(buf)) {
                buf.append(" AND (d.city_id=").append(cityId);
            } else {
                buf.append(" or d.city_id=").append(cityId);
            }
        }

        if (districtId != null) {
            if (StringUtils.isEmpty(buf)) {
                buf.append(" AND (d.district_id=").append(districtId);
            } else {
                buf.append(" or d.district_id=").append(districtId);
            }
        }

        if (streetId != null) {
            if (StringUtils.isEmpty(buf)) {
                buf.append(" AND (d.street_id=").append(streetId);
            } else {
                buf.append(" or d.street_id=").append(streetId);
            }
        }

        if (communityId != null) {
            if (StringUtils.isEmpty(buf)) {
                buf.append(" AND (d.community_id=").append(communityId);
            } else {
                buf.append(" or d.community_id=").append(communityId);
            }
        }

        if (StringUtils.isNotEmpty(buf)) {
            buf.append(")");
        }

        Map<String, String> map = new HashMap<>();
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql + buf.toString());
        if (list.isEmpty()) {
            log.info("不需要审批, sql={}", sql + buf.toString());
            // 结果为空，默认不需要审批
            map.put("code", "10000");
            map.put("status", "0");// 不需要审批
            map.put("orgId", null);// 审批企业ID
            return map;
        }

        // 查询公租房绑定产权单位审批人
        // 匹配社区住建局
        Long orgId = getAreaAuditStatus(list, communityId);
        // 匹配街道住建局
        if (orgId == null) {
            orgId = getAreaAuditStatus(list, streetId);
        }
        // 匹配区/县住建局
        if (orgId == null) {
            orgId = getAreaAuditStatus(list, districtId);
        }
        // 匹配市住建局
        if (orgId == null) {
            orgId = getAreaAuditStatus(list, cityId);
        }
        // 匹配省住建局
        if (orgId == null) {
            orgId = getAreaAuditStatus(list, provinceId);
        }

        if (orgId == null) {
            log.info("获取审批人信息失败, 未找到需要审批的住建局, orgId={}", orgId);
            map.put("code", "10000");
            map.put("status", "0");// 不需要审批
            map.put("orgId", null);// 审批人为节点审批人
            return map;
        }

        map.put("code", "10000");
        map.put("status", "1");// 需要审批
        map.put("orgId", String.valueOf(orgId));// 审批企业ID
        return map;
    }

    /**
     * 获取需要审批的住建局企业id
     *
     * @param list
     * @param areaId
     * @return
     * @author shun
     * @date 2021年6月30日
     */
    public Long getAreaAuditStatus(List<Map<String, Object>> list, Long areaId) {
        for (Map<String, Object> mp : list) {
            // 企业区域id
            Long id = Long.parseLong(mp.get("areaId").toString());
            if (areaId.longValue() == id.longValue() && mp.get("bindAuitStatus") != null) {
                Integer status = Integer.parseInt(mp.get("bindAuitStatus").toString());
                // 0不需要审批 1需要审批
                if (status == 1) {
                    Long orgId = Long.parseLong(mp.get("orgId").toString());
                    return orgId;
                }
            }
        }

        return null;
    }

    /**
     * 查询企业绑定产权单位审批人
     *
     * @param orgId
     * @return
     * @author shun
     * @date 2021年6月30日
     */
    public String findOrgAuditUser(Long orgId) {
        HouseAuditSet entity = houseAuditSetRepository.findByOrgId(orgId);
        if (entity == null) {
            return null;
        }

        return entity.getAuditId();
    }


    /**
     * 公租房数据
     */
    /**
     * 公租房管理模块首页公租房-数据 实现了公租房管理模块首页数据展示
     * @param accessToken 权限鉴定
     * @return
     */
    public ResultBean<Map<String, Object>> pageData(String accessToken) {
        //获取登录用户的权限
        TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
        //查询出公租房中有多少数据
        String SQL = " select * from saas_house h  ";
        //查询出公租房中有多少数据
        String count = " select COUNT(1) from saas_house h  ";
        //判断登录的用户是否有权限，如果没有权限不让使用
        if (tokenRedisVo == null) {
            return ResultBean.failedResultOfToken();
        }
        //获取用户的ID
        Long enterpriseId = tokenRedisVo.getEnterpriseId();
        //根据用户ID查询出用户层级权限
        DepartmentHierarchy hierarchy = departmentHierarchyRepository.findByOrgIdAndStatus(enterpriseId, 1);
        if (hierarchy == null) {
            return ResultBean.failedResultWithMsg("用户所属机构未设置层级架构");
        }
        //获取机构下有多少公租房
        StringBuilder where = new StringBuilder(" WHERE 1=1 and h.house_nature = 1  ");   //house_nature 性质:1公租房、2商品房、3经济适用房、5其它'
        List<Object> args = new ArrayList<>();
        
        where.append(" and h.org_id = ?  ");
        args.add(hierarchy.getOrgId());
        
        //根据区域标签所对应区域id查公租房
        // 20220328客户要求只查询自己产权单位的房子
        /*Long areaId = null;
        String areaTag = null;

        Long areaTagId = hierarchy.getTagId();//区域标签
        if (areaTagId == null) {
            return ResultBean.failedResultWithMsg("机构所对应区域标签为空");
        }

        if (areaTagId == 1) {
            areaTag = "province_id";
        } else if (areaTagId == 2) {
            areaTag = "city_id";
        } else if (areaTagId == 3) {
            areaTag = "district_id";
        } else if (areaTagId == 4) {
            areaTag = "street_id";
        } else if (areaTagId == 5) {
            areaTag = "community_id";
        }
        areaId = hierarchy.getAreaId();//区域id

        where.append(" and h." + areaTag + " = ?  ");
        args.add(areaId);*/

        Map<String, Object> map = new HashMap<>();

        //公租房总套数
        Long total = jdbcTemplate.queryForObject(count + where, args.toArray(), Long.class);

        //已绑定总套数  bind_status  '产权人绑定状态:0未绑定 1绑定'
        Long bindNum = jdbcTemplate.queryForObject(count + where + " and bind_status=1 ", args.toArray(), Long.class);

        //已配租总套数  match_status '配租状态：0未配租 1可配租 2已配租'
        Long matchNum = jdbcTemplate.queryForObject(count + where + " and match_status=2  ", args.toArray(), Long.class);

        //空置房总套数
        Long unMatchNum = total - matchNum;


        //查询数据
        // List<House> data = jdbcTemplate.query(SQL + where, new BeanPropertyRowMapper<>(House.class), args.toArray());
        //把数据封装进map里面
        map.put("total", total);
        map.put("bindNum", bindNum);
        map.put("matchNum", matchNum);
        map.put("unMatchNum", unMatchNum);
        //把数据结果传给前端页面
        return ResultBean.successfulResult(map);
    }


    /**
     * 公租房-人员数据
     */
    /**
     * 公租房管理模块首页公租房-人员数据，实现了公租房人员数据和公租房人员已配租数据的展示
     * @param accessToken 权限鉴定
     * @return
     */
    public ResultBean<Map<String, Object>> peopleData(String accessToken) {
        //获取登录用户的权限
        TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
        //查询出公租房资格申请审批通过的户数
        String SQL = " select * from saas_house_eligible_apply h  ";
        //查询出公租房资格申请审批通过的户数
        String count = " select COUNT(1) from saas_house_eligible_apply h  ";
        //判断登录的用户是否有权限，如果没有权限不让使用
        if (tokenRedisVo == null) {
            return ResultBean.failedResultOfToken();
        }
        //获取用户的ID
        Long enterpriseId = tokenRedisVo.getEnterpriseId();
        //根据用户ID查询出用户层级权限
        DepartmentHierarchy hierarchy = departmentHierarchyRepository.findByOrgIdAndStatus(enterpriseId, 1);
        if (hierarchy == null) {
            return ResultBean.failedResultWithMsg("用户所属机构未设置层级架构");
        }

        //根据区域标签所对应区域id查公租房
        Long areaId = null;
        String areaTag = null;
        //获取区域标签
        Long areaTagId = hierarchy.getTagId();//区域标签
        if (areaTagId == null) {
            return ResultBean.failedResultWithMsg("机构所对应区域标签为空");
        }
        //判读出对应的区域标签，然后查出不同区域的人员数据总数
        if (areaTagId == 1) {
            areaTag = "province_id";
        } else if (areaTagId == 2) {
            areaTag = "city_id";
        } else if (areaTagId == 3) {
            areaTag = "district_id";
        } else if (areaTagId == 4) {
            areaTag = "street_id";
        } else if (areaTagId == 5) {
            areaTag = "community_id";
        }
        areaId = hierarchy.getAreaId();//区域id
        //获取机构下有多少公租房资格申请审批通过的户数
        StringBuilder where = new StringBuilder(" WHERE 1=1  ");
        List<Object> args = new ArrayList<>();

        where.append(" and h." + areaTag + " = ?  ");
        args.add(areaId);

        Map<String, Object> map = new HashMap<>();

        // apply_status '申请状态：0草稿、1未审核 2审批中 3已审核 4审核不通过 6已撤销 9已过期'
        //总户数：公租房资格申请审批通过的户数，以承租人为依据统计
        Long totalHouse = jdbcTemplate.queryForObject(count + where + " and apply_status=3 ", args.toArray(), Long.class);

        List<Eligible> totalHouseData = jdbcTemplate.query(SQL + where + " and apply_status=3 ", new BeanPropertyRowMapper<>(Eligible.class), args.toArray());

        //总人数：公租房资格申请审批通过的总人数（承租人+同住人）
        List<Long> applyIds = totalHouseData.stream().map(Eligible::getId).collect(Collectors.toList());
        List<Cohabit> cohabit = cohabitRepository.findByApplyIdIn(applyIds);

        Long totalPeople = totalHouse + cohabit.size();

        // match_status '配租状态：1轮候配租 2待配租 3已完成'
        //已配租户数：已配租公租房的承租人数量
        Long matchHouse = jdbcTemplate.queryForObject(count + where + " and apply_status=3 and match_status=3  ", args.toArray(), Long.class);

        List<Eligible> matchHouseData = jdbcTemplate.query(SQL + where + " and apply_status=3 and match_status=3  ", new BeanPropertyRowMapper<>(Eligible.class), args.toArray());

        //已配租人数：已配租公租房的承租人+同住人数量
        List<Long> applyIds1 = matchHouseData.stream().map(Eligible::getId).collect(Collectors.toList());
        List<Cohabit> cohabit1 = cohabitRepository.findByApplyIdIn(applyIds1);

        Long matchPeople = matchHouse + cohabit1.size();

        map.put("totalHouse", totalHouse);  //总户数
        map.put("totalPeople", totalPeople);  //总人数

        map.put("matchHouse", matchHouse); //已配租户数
        map.put("matchPeople", matchPeople);  //已配租人数

        map.put("unmatchHouse", totalHouse - matchHouse); //未配租户数：资格申请通过，未进行配租的承租人数量
        map.put("unmatchPeople", totalPeople - matchPeople); //未配租人数：资格申请通过，未进行配租的承租人+同住人数量

        //公租房人员已配租数据
        // attribute_type  '人员属性：1城镇低保住房困难家庭、2中低收入住房困难家庭、3外来务工人员、4新就业人员、5引进人才'

        Integer house1 = 0;
        Integer house2 = 0;
        Integer house3 = 0;
        Integer house4 = 0;
        Integer house5 = 0;
        Integer people1 = 0;
        Integer people2 = 0;
        Integer people3 = 0;
        Integer people4 = 0;
        Integer people5 = 0;
        // 获取公租房人员数据
        Map<Integer, List<Eligible>> eligibleMap = matchHouseData.stream().collect(Collectors.groupingBy(Eligible::getAttributeType));
        //遍历循环公租房人员数据
        for (Integer attributeType : eligibleMap.keySet()) {
            List<Eligible> dataList = eligibleMap.get(attributeType);
            //判断出对应得公租房人员已配租数据对应的人员属性的总数
            if (attributeType.intValue() == 1) {
                house1 = dataList.size();
                List<Long> applyIdList = dataList.stream().map(Eligible::getId).collect(Collectors.toList());
                List<Cohabit> cohabitList = cohabitRepository.findByApplyIdIn(applyIdList);
                people1 = house1 + cohabitList.size();
            }
            if (attributeType.intValue() == 2) {
                house2 = dataList.size();
                List<Long> applyIdList = dataList.stream().map(Eligible::getId).collect(Collectors.toList());
                List<Cohabit> cohabitList = cohabitRepository.findByApplyIdIn(applyIdList);
                people2 = house2 + cohabitList.size();
            }
            if (attributeType.intValue() == 3) {
                house3 = dataList.size();
                List<Long> applyIdList = dataList.stream().map(Eligible::getId).collect(Collectors.toList());
                List<Cohabit> cohabitList = cohabitRepository.findByApplyIdIn(applyIdList);
                people3 = house3 + cohabitList.size();
            }
            if (attributeType.intValue() == 4) {
                house4 = dataList.size();
                List<Long> applyIdList = dataList.stream().map(Eligible::getId).collect(Collectors.toList());
                List<Cohabit> cohabitList = cohabitRepository.findByApplyIdIn(applyIdList);
                people4 = house4 + cohabitList.size();
            }
            if (attributeType.intValue() == 5) {
                house5 = dataList.size();
                List<Long> applyIdList = dataList.stream().map(Eligible::getId).collect(Collectors.toList());
                List<Cohabit> cohabitList = cohabitRepository.findByApplyIdIn(applyIdList);
                people5 = house5 + cohabitList.size();
            }
        }
        //把对应的数据存储到map中
        map.put("house1", house1);
        map.put("people1", people1);

        map.put("house2", house2);
        map.put("people2", people2);

        map.put("house3", house3);
        map.put("people3", people3);

        map.put("house4", house4);
        map.put("people4", people4);

        map.put("house5", house5);
        map.put("people5", people5);

        // type  '申请类型(1个人申请 2单位申请)'
        Integer house6 = 0;
        Integer people6 = 0;
        Integer house7 = 0;
        Integer people7 = 0;
        // 获取公租房人员数据
        Map<Integer, List<Eligible>> typeMap = matchHouseData.stream().collect(Collectors.groupingBy(Eligible::getType));
        //遍历循环公租房人员数据
        for (Integer type : typeMap.keySet()) {
            //判断出对应得公租房人员数据对应的人员属性的总数
            List<Eligible> dataList = typeMap.get(type);

            if (type.intValue() == 2) {
                house6 = dataList.size();
                List<Long> applyIdList = dataList.stream().map(Eligible::getId).collect(Collectors.toList());
                List<Cohabit> cohabitList = cohabitRepository.findByApplyIdIn(applyIdList);
                people6 = house6 + cohabitList.size();
            }
            if (type.intValue() == 1) {
                house7 = dataList.size();
                List<Long> applyIdList = dataList.stream().map(Eligible::getId).collect(Collectors.toList());
                List<Cohabit> cohabitList = cohabitRepository.findByApplyIdIn(applyIdList);
                people7 = house7 + cohabitList.size();
            }
        }
        //把对应的数据存储到map中
        map.put("house6", house6);
        map.put("people6", people6);

        map.put("house7", house7);
        map.put("people7", people7);
        //把数据结果传给前端页面
        return ResultBean.successfulResult(map);
    }
    /**
     * 开门记录数据，实现了90天未开门房屋户数数据的展示
     * @param accessToken 权限鉴定
     * @return
     */
    public ResultBean<List<Map<String, Object>>> lockData(String accessToken) {
        //获取用户登录权限
        TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
        //查询出对应用户的开门记录
        String SQL = " select sl.* from saas_smart_lock sl inner join saas_house h on sl.house_id=h.id  ";
        //用户权限的鉴定
        if (tokenRedisVo == null) {
            return ResultBean.failedResultOfToken();
        }
        //获取用户ID
        Long enterpriseId = tokenRedisVo.getEnterpriseId();
        //根据用户ID查询出用户层级权限
        DepartmentHierarchy hierarchy = departmentHierarchyRepository.findByOrgIdAndStatus(enterpriseId, 1);
        if (hierarchy == null) {
            return ResultBean.failedResultWithMsg("用户所属机构未设置层级架构");
        }

        //根据区域标签所对应区域id查公租房
        Long areaId = null;
        String areaTag = null;
        //获取区域标签
        Long areaTagId = hierarchy.getTagId();//区域标签
        //如果区域标签为空，直接返回机构所对应区域标签为空
        if (areaTagId == null) {
            return ResultBean.failedResultWithMsg("机构所对应区域标签为空");
        }
        //根据区域标签查询出对应的数据
        if (areaTagId == 1) {
            areaTag = "province_id";
        } else if (areaTagId == 2) {
            areaTag = "city_id";
        } else if (areaTagId == 3) {
            areaTag = "district_id";
        } else if (areaTagId == 4) {
            areaTag = "street_id";
        } else if (areaTagId == 5) {
            areaTag = "community_id";
        }
        areaId = hierarchy.getAreaId();//区域id
        //拼接区域为公租房的数据
        StringBuilder where = new StringBuilder(" WHERE 1=1 and h.house_nature = 1  ");   //house_nature 性质:1公租房、2商品房、3经济适用房、5其它'
        List<Object> args = new ArrayList<>();

        where.append(" and h." + areaTag + " = ?  ");
        args.add(areaId);

        //未开门公租房数据，查询saas_smart_lock表，条件是累计未开门时间超过90天的   [ total_close_day  累计未开门天数]
        List<Map<String, Object>> data = jdbcTemplate.queryForList(SQL + where + " and sl.total_close_day > 90  ", args.toArray());

        List<String> matchUserIds = new ArrayList<>();
        //获取累计未开门时间超过90天的用户
        data.forEach(m -> {
            matchUserIds.add((String) m.get("match_user_id"));
        });
        //根据承租人ID查询出承租人的数据
        List<AppUser> appUserList = appUserRepository.findByEnterpriseUserIdIn(matchUserIds);
        //遍历累计未开门时间超过90天的用户
        for (Map<String, Object> m : data) {
            //遍历每一个用户
            for (AppUser user : appUserList) {
                //根据用户ID跟累计未开门时间超过90天的用户的ID进行比对，从而查询出累计未开门时间超过90天的用户
                if (user.getEnterpriseUserId().equals(m.get("match_user_id"))) {
                    m.put("match_user_phone", user.getPhone());
                }
            }
        }

        //把数据返回给前端页面
        return ResultBean.successfulResult(data);
    }

    /**
     * 公租房-缴费数据，[租金/押金]实现了公租房缴费数据的展示
     * @param accessToken 权限鉴定
     * @return
     */
    public ResultBean<Object> payData(String accessToken) {
        //获取用户的权限
        TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
        //查询出对应承租人的缴费记录
        String SQL = " select b.* from saas_rent_bill b inner join saas_house h on b.house_id=h.id  ";
        //用户权限的鉴定
        if (tokenRedisVo == null) {
            return ResultBean.failedResultOfToken();
        }
        //获取用户ID
        Long enterpriseId = tokenRedisVo.getEnterpriseId();
        //根据用户ID查询出用户层级权限
        DepartmentHierarchy hierarchy = departmentHierarchyRepository.findByOrgIdAndStatus(enterpriseId, 1);
        if (hierarchy == null) {
            return ResultBean.failedResultWithMsg("用户所属机构未设置层级架构");
        }

        //根据区域标签所对应区域id查公租房
        Long areaId = null;
        String areaTag = null;
        //获取区域标签
        Long areaTagId = hierarchy.getTagId();//区域标签
        //如果区域标签为空，直接返回机构所对应区域标签为空
        if (areaTagId == null) {
            return ResultBean.failedResultWithMsg("机构所对应区域标签为空");
        }
        //根据区域标签查询出对应的数据
        if (areaTagId == 1) {
            areaTag = "province_id";
        } else if (areaTagId == 2) {
            areaTag = "city_id";
        } else if (areaTagId == 3) {
            areaTag = "district_id";
        } else if (areaTagId == 4) {
            areaTag = "street_id";
        } else if (areaTagId == 5) {
            areaTag = "community_id";
        }
        areaId = hierarchy.getAreaId();//区域id
        //拼接区域为公租房的数据
        StringBuilder where = new StringBuilder(" WHERE 1=1 and h.house_nature = 1  ");   //house_nature 性质:1公租房、2商品房、3经济适用房、5其它'
        List<Object> args = new ArrayList<>();

        where.append(" and h." + areaTag + " = ?  ");
        args.add(areaId);

        Map<String, Object> map = new HashMap<>();

        //缴费数据，主要针对租金/押金 payment_status  '缴费状态：0未缴费 1已缴费'
        //查询出正常缴费的记录
        List<Map<String, Object>> normaldata = jdbcTemplate.queryForList(SQL + where + " and b.payment_status = 1 and b.payment_time <= b.should_pay_time ", args.toArray());

        //正常缴费金额
        BigDecimal normalCost = new BigDecimal(0);
        //遍历正常缴费金额
        for (Map<String, Object> m : normaldata) {
            // cost  '费用 单位分'
            Integer c = (Integer) m.get("cost");
            BigDecimal cost = new BigDecimal(c);
            normalCost = normalCost.add(cost);
        }
        //遍历获取正常缴费的房间数量
        Map<String, List<Map<String, Object>>> normaldataMap = normaldata.stream().collect(Collectors.groupingBy((Map<String, Object> m) -> (String) m.get("house_id")));
        //正常缴费套数
        long normalNum = 0L;
        normalNum = normaldataMap.keySet().size();
        //计算出正常缴费金额，因为后端单位是分，前端单位是元，所以需要把数据除以100
        map.put("normalCost", normalCost.divide(new BigDecimal(100), 2, RoundingMode.HALF_UP));  //正常缴费金额
        map.put("normalNum", normalNum);    //正常缴费套数

        //查询出正常欠费的记录
        List<Map<String, Object>> normalNoPayData = jdbcTemplate.queryForList(SQL + where + " and b.payment_status = 0 and DATE_FORMAT(now(),'%Y%m%d') <= DATE_FORMAT(b.should_pay_time,'%Y%m%d') ", args.toArray());

        //正常欠费金额
        BigDecimal normalNoPayCost = new BigDecimal(0);
        //正常欠费套数
        long normalNoPayNum = 0L;
        //遍历正常欠费金额
        for (Map<String, Object> m : normalNoPayData) {
            // cost  '费用 单位分'
            Integer c = (Integer) m.get("cost");
            BigDecimal cost = new BigDecimal(c);
            normalNoPayCost = normalNoPayCost.add(cost);
        }
        //遍历获取正常欠费的房间数量
        Map<String, List<Map<String, Object>>> normalNoPayDataMap = normalNoPayData.stream().collect(Collectors.groupingBy((Map<String, Object> m) -> (String) m.get("house_id")));
        normalNoPayNum = normalNoPayDataMap.keySet().size();
        //计算出正常欠费金额，因为后端单位是分，前端单位是元，所以需要把数据除以100
        map.put("normalNoPayCost", normalNoPayCost.divide(new BigDecimal(100), 2, RoundingMode.HALF_UP)); //正常欠费金额
        map.put("normalNoPayNum", normalNoPayNum);  //正常欠费套数
        //查询出逾期欠费的记录
        List<Map<String, Object>> noPayData = jdbcTemplate.queryForList(SQL + where + " and b.payment_status=0 and DATE_FORMAT(now(),'%Y%m%d') > DATE_FORMAT(b.should_pay_time,'%Y%m%d') ", args.toArray());

        //逾期欠费金额
        BigDecimal noPayCost = new BigDecimal(0);
        //逾期欠费套数
        long noPayNum = 0L;
        //遍历逾期欠费金额
        for (Map<String, Object> m : noPayData) {
            // cost  '费用 单位分'
            Integer c = (Integer) m.get("cost");
            BigDecimal cost = new BigDecimal(c);
            noPayCost = noPayCost.add(cost);
        }
        //遍历获取逾期欠费的房间数量
        Map<String, List<Map<String, Object>>> noPayDataMap = noPayData.stream().collect(Collectors.groupingBy((Map<String, Object> m) -> (String) m.get("house_id")));
        noPayNum = noPayDataMap.keySet().size();
        //计算出正常欠费金额，因为后端单位是分，前端单位是元，所以需要把数据除以100
        map.put("noPayCost", noPayCost.divide(new BigDecimal(100), 2, RoundingMode.HALF_UP));  //逾期欠费金额
        map.put("noPayNum", noPayNum);   //逾期欠费套数
        //把数据返回给前端页面
        return ResultBean.successfulResult(map);
    }

    /**
     * 公租房-缴费数据详情，[租金/押金],实现公租房缴费数据详情的查询
     * @param accessToken 权限鉴定
     * @param pageNumber 第条数据
     * @param pageSize  总共查询出多少条
     * @param type 标签
     * @return
     */
    public ResultBean<Object> payDetailsData(String accessToken, Integer pageNumber, Integer pageSize, Integer type) {
        //获取用户登录权限
        TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
        //查询出对应用户的缴费记录
        String SQL = " select b.* from saas_rent_bill b inner join saas_house h on b.house_id=h.id  ";
        //查询出对应用户的缴费总数记录
        String count = " select count(1) from saas_rent_bill b inner join saas_house h on b.house_id=h.id  ";
        //用户权限的鉴定
        if (tokenRedisVo == null) {
            return ResultBean.failedResultOfToken();
        }
        //获取用户ID
        Long enterpriseId = tokenRedisVo.getEnterpriseId();
        //根据用户ID查询出用户层级权限
        DepartmentHierarchy hierarchy = departmentHierarchyRepository.findByOrgIdAndStatus(enterpriseId, 1);
        if (hierarchy == null) {
            return ResultBean.failedResultWithMsg("用户所属机构未设置层级架构");
        }

        //根据区域标签所对应区域id查公租房
        Long areaId = null;
        String areaTag = null;
        //获取区域标签
        Long areaTagId = hierarchy.getTagId();//区域标签
        if (areaTagId == null) {
            return ResultBean.failedResultWithMsg("机构所对应区域标签为空");
        }
        //根据区域标签查询出对应的数据
        if (areaTagId == 1) {
            areaTag = "province_id";
        } else if (areaTagId == 2) {
            areaTag = "city_id";
        } else if (areaTagId == 3) {
            areaTag = "district_id";
        } else if (areaTagId == 4) {
            areaTag = "street_id";
        } else if (areaTagId == 5) {
            areaTag = "community_id";
        }
        areaId = hierarchy.getAreaId();//区域id
        //拼接区域为公租房的数据
        StringBuilder where = new StringBuilder(" WHERE 1=1 and h.house_nature = 1  ");   //house_nature 性质:1公租房、2商品房、3经济适用房、5其它'
        List<Object> args = new ArrayList<>();

        where.append(" and h." + areaTag + " = ?  ");
        args.add(areaId);

        Map<String, Object> map = new HashMap<>();

        //缴费数据，主要针对租金/押金 payment_status  '缴费状态：0未缴费 1已缴费'
        //获取已经缴费的数据
        Long normaltotal = jdbcTemplate.queryForObject(count + where + " and b.payment_status = 1 and b.payment_time <= b.should_pay_time ", Long.class, args.toArray());
        //获取未缴费的数据
        Long normalNoPaytotal = jdbcTemplate.queryForObject(count + where + " and b.payment_status = 0 and DATE_FORMAT(now(),'%Y%m%d') <= DATE_FORMAT(b.should_pay_time,'%Y%m%d') ", Long.class, args.toArray());
        //获取预期未缴费的数据
        Long noPaytotal = jdbcTemplate.queryForObject(count + where + " and b.payment_status = 0 and DATE_FORMAT(now(),'%Y%m%d') > DATE_FORMAT(b.should_pay_time,'%Y%m%d') ", Long.class, args.toArray());
        //查询分页数据
        String limitWhere = " ORDER BY b.create_time desc limit ? offset ?  ";
        //设置从第几条查询
        args.add(pageSize);
        //设置查询多少条数据
        args.add(pageNumber * pageSize);
        //存储已经缴费的数据
        List<Map<String, Object>> normaldata = jdbcTemplate.queryForList(SQL + where + " and b.payment_status=1 and b.payment_time <= b.should_pay_time " + limitWhere, args.toArray());
        //存储未缴费的数据
        List<Map<String, Object>> normalNoPayData = jdbcTemplate.queryForList(SQL + where + " and b.payment_status = 0 and DATE_FORMAT(now(),'%Y%m%d') <= DATE_FORMAT(b.should_pay_time,'%Y%m%d') " + limitWhere, args.toArray());
        //存储预期未缴费的数据
        List<Map<String, Object>> noPayData = jdbcTemplate.queryForList(SQL + where + " and b.payment_status = 0 and DATE_FORMAT(now(),'%Y%m%d') > DATE_FORMAT(b.should_pay_time,'%Y%m%d') " + limitWhere, args.toArray());

        List<String> matchUserIds = new ArrayList<>(); //承租人
        List<String> communityIds = new ArrayList<>();//小区
        List<Long> buildingNums = new ArrayList<>(); //楼栋
        List<Long> units = new ArrayList<>(); //单元
        //遍历存储已经缴费的数据
        normaldata.forEach(m -> {
            matchUserIds.add((String) m.get("match_user_id"));
            communityIds.add((String) m.get("community_id"));
            buildingNums.add((Long) m.get("building_num"));
            units.add((Long) m.get("unit"));
        });
        //遍历存储未缴费的数据
        normalNoPayData.forEach(m -> {
            matchUserIds.add((String) m.get("match_user_id"));
            communityIds.add((String) m.get("community_id"));
            buildingNums.add((Long) m.get("building_num"));
            units.add((Long) m.get("unit"));
        });
        //遍历存储预期未缴费的数据
        noPayData.forEach(m -> {
            matchUserIds.add((String) m.get("match_user_id"));
            communityIds.add((String) m.get("community_id"));
            buildingNums.add((Long) m.get("building_num"));
            units.add((Long) m.get("unit"));
        });
        //查询出所有承租人缴费的数据
        List<AppUser> appUserList = appUserRepository.findByIdOrEnterpriseIn(matchUserIds, matchUserIds);
        //查询出所有小区缴费的数据
        List<Community> communityList = communityRepository.findByIdIn(communityIds);
        //查询出所有楼栋缴费的数据
        List<CommunityBuild> communityBuildList = communityBuildRepository.findByIdIn(buildingNums);
        //查询出所有单元缴费的数据
        List<CommunityBuildUnit> communityBuildUnitList = communityBuildUnitRepository.findByIdIn(units);
        //遍历已经缴费的数据
        for (Map<String, Object> m : normaldata) {
            // cost  '费用 单位分'
            Integer c = (Integer) m.get("cost");
            BigDecimal cost = new BigDecimal(c);
            //计算出正常欠费金额，因为后端单位是分，前端单位是元，所以需要把数据除以100
            m.put("cost", cost.divide(new BigDecimal(100), 2, RoundingMode.HALF_UP));
            //遍历所有承租人缴费的数据
            for (AppUser user : appUserList) {
                //根据个人用户ID对比出对应的数据
                if (user.getId() != null && user.getId().equals(m.get("match_user_id"))) {
                    m.put("match_user_phone", user.getPhone());
                    m.put("match_user_name", user.getName());
                }
                //根据企业ID对比出对应的数据
                if (user.getEnterpriseUserId() != null && user.getEnterpriseUserId().equals(m.get("match_user_id"))) {
                    m.put("match_user_phone", user.getPhone());
                    m.put("match_user_name", user.getName());
                }
            }
            //遍历出所有小区缴费的数据
            for (Community community : communityList) {
                String communityId = (String) m.get("community_id");
                if (community.getId().equals(communityId)) {
                    m.put("community_name", community.getName());
                }
            }
            //遍历出所有楼栋缴费的数据
            for (CommunityBuild communityBuild : communityBuildList) {
                Long buildingNum = (Long) m.get("building_num");
                if (communityBuild.getId().equals(buildingNum)) {
                    m.put("building_name", communityBuild.getName());
                }
            }
            //遍历出所有单元缴费的数据
            for (CommunityBuildUnit communityBuildUnit : communityBuildUnitList) {
                Long unit = (Long) m.get("unit");
                if (communityBuildUnit.getId().equals(unit)) {
                    m.put("unit_name", communityBuildUnit.getName());
                }
            }
        }
        //遍历未缴费的数据
        for (Map<String, Object> m : normalNoPayData) {
            // cost  '费用 单位分'
            Integer c = (Integer) m.get("cost");
            BigDecimal cost = new BigDecimal(c);
            //计算出正常欠费金额，因为后端单位是分，前端单位是元，所以需要把数据除以100
            m.put("cost", cost.divide(new BigDecimal(100), 2, RoundingMode.HALF_UP));
            //遍历所有承租人缴费的数据
            for (AppUser user : appUserList) {
                //根据个人用户ID对比出对应的数据
                if (user.getId() != null && user.getId().equals(m.get("match_user_id"))) {
                    m.put("match_user_phone", user.getPhone());
                    m.put("match_user_name", user.getName());
                }
                //根据企业ID对比出对应的数据
                if (user.getEnterpriseUserId() != null && user.getEnterpriseUserId().equals(m.get("match_user_id"))) {
                    m.put("match_user_phone", user.getPhone());
                    m.put("match_user_name", user.getName());
                }
            }
            //遍历出所有小区缴费的数据
            for (Community community : communityList) {
                String communityId = (String) m.get("community_id");
                if (community.getId().equals(communityId)) {
                    m.put("community_name", community.getName());
                }
            }
            //遍历出所有楼栋缴费的数据
            for (CommunityBuild communityBuild : communityBuildList) {
                Long buildingNum = (Long) m.get("building_num");
                if (communityBuild.getId().equals(buildingNum)) {
                    m.put("building_name", communityBuild.getName());
                }
            }
            //遍历出所有单元缴费的数据
            for (CommunityBuildUnit communityBuildUnit : communityBuildUnitList) {
                Long unit = (Long) m.get("unit");
                if (communityBuildUnit.getId().equals(unit)) {
                    m.put("unit_name", communityBuildUnit.getName());
                }
            }
        }

        for (Map<String, Object> m : noPayData) {
            // cost  '费用 单位分'
            Integer c = (Integer) m.get("cost");
            BigDecimal cost = new BigDecimal(c);
            //计算出正常欠费金额，因为后端单位是分，前端单位是元，所以需要把数据除以100
            m.put("cost", cost.divide(new BigDecimal(100), 2, RoundingMode.HALF_UP));

            m.put("overTime", daysBetween((Date) m.get("end_time"), new Date()));
            //遍历所有承租人缴费的数据
            for (AppUser user : appUserList) {
                //根据个人用户ID对比出对应的数据
                if (user.getId() != null && user.getId().equals(m.get("match_user_id"))) {
                    m.put("match_user_phone", user.getPhone());
                    m.put("match_user_name", user.getName());
                }
                //根据企业ID对比出对应的数据
                if (user.getEnterpriseUserId() != null && user.getEnterpriseUserId().equals(m.get("match_user_id"))) {
                    m.put("match_user_phone", user.getPhone());
                    m.put("match_user_name", user.getName());
                }
            }
            //遍历出所有小区缴费的数据
            for (Community community : communityList) {
                String communityId = (String) m.get("community_id");
                if (community.getId().equals(communityId)) {
                    m.put("community_name", community.getName());
                }
            }
            //遍历出所有楼栋缴费的数据
            for (CommunityBuild communityBuild : communityBuildList) {
                Long buildingNum = (Long) m.get("building_num");
                if (communityBuild.getId().equals(buildingNum)) {
                    m.put("building_name", communityBuild.getName());
                }
            }
            //遍历出所有单元缴费的数据
            for (CommunityBuildUnit communityBuildUnit : communityBuildUnitList) {
                Long unit = (Long) m.get("unit");
                if (communityBuildUnit.getId().equals(unit)) {
                    m.put("unit_name", communityBuildUnit.getName());
                }
            }
        }

        // type :  1 正常缴费数据, 2 正常欠费数据, 3 逾期欠费数据
        //根据对比type的不同存储对应的数据
        if (type.intValue() == 1) {
            map.put("data", normaldata);
            map.put("total", normaltotal);
        }
        //根据对比type的不同存储对应的数据
        if (type.intValue() == 2) {
            map.put("data", normalNoPayData);
            map.put("total", normalNoPaytotal);
        }
        //根据对比type的不同存储对应的数据
        if (type.intValue() == 3) {
            map.put("data", noPayData);
            map.put("total", noPaytotal);
        }
        //把存储的数据输出给前端页面
        return ResultBean.successfulResult(map);
    }

    /**
     * 计算两个日期相差多少天(by date)
     */
    public int daysBetween(Date date1, Date date2) {
        Calendar cal = Calendar.getInstance();
        cal.setTime(date1);
        long time1 = cal.getTimeInMillis();
        cal.setTime(date2);
        long time2 = cal.getTimeInMillis();
        long between_days = (time2 - time1) / (1000 * 3600 * 24);
        return Integer.parseInt(String.valueOf(between_days));
    }

    /**
     * 公租房数据-导出，用来导出公租房的数据，实现了不同公租房对应的数据导出
     * @param accessToken 权限鉴定
     * @param response  数据响应
     * @param type  标签
     * @throws Exception
     */
    public void pageDataExport(String accessToken, HttpServletResponse response, Integer type) throws Exception {
        //日志记录和权限
        log.info("houseService  pageDataExport  accessToken={} ", accessToken);

        List<Map<String, Object>> data = new ArrayList<>();
        // 表格头
        Map<String, String> map = new LinkedHashMap<>();
        // 文件名
        String fileName = "";

        //type : 1 公租房数据 , 2 公租房人员数据 , 3 公租房人员已配租数据
        //根据type判断出对应要导出的数据
        if (type.intValue() == 1) {
            Map<String, Object> m = new HashMap<>();
            //获取当前登录用户对应的数据
            m = this.pageData(accessToken).getResultData();
            data.add(m);

            map.put("公租房总套数", "total");
            map.put("已绑定总套数", "bindNum");
            map.put("已配租总套数", "matchNum");
            map.put("空置房总套数", "unMatchNum");

            fileName = "公租房数据_" + System.currentTimeMillis();
        }
        if (type.intValue() == 2) {
            Map<String, Object> m = new HashMap<>();
            m = this.peopleData(accessToken).getResultData();
            data.add(m);

            map.put("总户数", "totalHouse");  //总户数
            map.put("总人数", "totalPeople");  //总人数

            map.put("已配租户数", "matchHouse"); //已配租户数
            map.put("已配租人数", "matchPeople");  //已配租人数

            map.put("未配租户数", "unmatchHouse"); //未配租户数：资格申请通过，未进行配租的承租人数量
            map.put("未配租人数", "unmatchPeople"); //未配租人数：资格申请通过，未进行配租的承租人+同住人数量

            fileName = "公租房人员数据_" + System.currentTimeMillis();
        }
        if (type.intValue() == 3) {
            Map<String, Object> m = new HashMap<>();
            m = this.peopleData(accessToken).getResultData();
            data.add(m);
            // attribute_type  '人员属性：1城镇低保住房困难家庭、2中低收入住房困难家庭、3外来务工人员、4新就业人员、5引进人才'
            map.put("城镇低保住房困难家庭户数", "house1");
            map.put("城镇低保住房困难家庭人数", "people1");

            map.put("中低收入住房困难家庭户数", "house2");
            map.put("中低收入住房困难家庭人数", "people2");

            map.put("外来务工人员户数", "house3");
            map.put("外来务工人员人数", "people3");

            map.put("新就业人员户数", "house4");
            map.put("新就业人员人数", "people4");

            map.put("引进人才户数", "house5");
            map.put("引进人才人数", "people5");

            map.put("单位使用户数", "house6");
            map.put("单位使用人数", "people6");

            map.put("其他户数", "house7");
            map.put("其他人数", "people7");

            fileName = "公租房人员已配租数据_" + System.currentTimeMillis();
        }

        // 使用Export2Util工具进行文件导出
        Workbook workbook = Export2Util.getExcel4(data, map);
        response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");

        // 导出文件下载
        DownloadUtil downloadUtil = new DownloadUtil();
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        workbook.write(bos);
        //导出的格式为.xlsx
        downloadUtil.download(bos, response, URLEncoder.encode(fileName + ".xlsx", "utf-8"));
        // 关闭
        workbook.close();

    }

    /**
     * 开门记录数据-导出，实现了90天内未开门的承租人数据的导出
     *
     * @param accessToken 权限鉴定
     * @param response    数据响应
     * @throws Exception
     */
    public void lockDataExport(String accessToken, HttpServletResponse response) throws Exception {
        //日志记录和权限
        log.info("houseService  lockDataExport  accessToken={} ", accessToken);

        List<Map<String, Object>> data = new ArrayList<>();
        // 表格头
        Map<String, String> map = new LinkedHashMap<>();
        // 文件名
        String fileName = "";

        List<Map<String, Object>> list = new ArrayList<>();
        //获取当前用户登录的数据
        list = this.lockData(accessToken).getResultData();
        data.addAll(list);
        //遍历数据并进行存储
        for (Map<String, Object> m : data) {

            String information = (String) m.get("community_name") + m.get("build_name") + m.get("unit_name") + m.get("room_number");
            m.put("house_information", information);
        }
        //把数据存储到map中
        map.put("房屋ID", "house_id");
        map.put("房屋信息", "house_information");
        map.put("承租人", "match_user_name");
        map.put("手机号", "match_user_phone");
        map.put("未开门时间（天）", "total_close_day");

        fileName = "未开门房屋数据_" + System.currentTimeMillis();
        // 使用Export2Util工具进行文件导出
        Workbook workbook = Export2Util.getExcel4(data, map);
        response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");

        // 导出文件下载
        DownloadUtil downloadUtil = new DownloadUtil();
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        workbook.write(bos);
        //导出的格式为.xlsx
        downloadUtil.download(bos, response, URLEncoder.encode(fileName + ".xlsx", "utf-8"));
        // 关闭
        workbook.close();

    }

    /**
     * 公租房-缴费数据详情-导出,实现了公租房缴费数据详情的导出
     *
     * @param accessToken 权限鉴定
     * @param pageNumber  条数
     * @param pageSize    总条数
     * @param type        标签
     * @param response    数据响应
     * @return
     * @throws Exception
     */
    public ResultBean<Object> payDetailsDataExport(String accessToken, HttpServletResponse response, Integer pageNumber, Integer pageSize, Integer type) throws Exception {
        //日志记录和权限
        log.info("houseService  payDetailsDataExport  accessToken={} ", accessToken);
        //获取用户登录权限
        TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
        //查询出对应用户的缴费记录
        String SQL = " select b.* from saas_rent_bill b inner join saas_house h on b.house_id=h.id  ";
        //查询出对应用户的缴费总数
        String count = " select count(1) from saas_rent_bill b inner join saas_house h on b.house_id=h.id  ";
        //用户权限的鉴定
        if (tokenRedisVo == null) {
            return ResultBean.failedResultOfToken();
        }
        //获取用户ID
        Long enterpriseId = tokenRedisVo.getEnterpriseId();
        //根据用户ID查询出用户层级权限
        DepartmentHierarchy hierarchy = departmentHierarchyRepository.findByOrgIdAndStatus(enterpriseId, 1);
        if (hierarchy == null) {
            return ResultBean.failedResultWithMsg("用户所属机构未设置层级架构");
        }

        //根据区域标签所对应区域id查公租房
        Long areaId = null;
        String areaTag = null;
        //获取区域标签
        Long areaTagId = hierarchy.getTagId();//区域标签
        //如果区域标签为空，直接返回机构所对应区域标签为空
        if (areaTagId == null) {
            return ResultBean.failedResultWithMsg("机构所对应区域标签为空");
        }
        //根据区域标签查询出对应的数据
        if (areaTagId == 1) {
            areaTag = "province_id";
        } else if (areaTagId == 2) {
            areaTag = "city_id";
        } else if (areaTagId == 3) {
            areaTag = "district_id";
        } else if (areaTagId == 4) {
            areaTag = "street_id";
        } else if (areaTagId == 5) {
            areaTag = "community_id";
        }
        areaId = hierarchy.getAreaId();//区域id
        //拼接区域为公租房的数据
        StringBuilder where = new StringBuilder(" WHERE 1=1 and h.house_nature = 1  ");   //house_nature 性质:1公租房、2商品房、3经济适用房、5其它'
        List<Object> args = new ArrayList<>();

        where.append(" and h." + areaTag + " = ?  ");
        args.add(areaId);

        //缴费数据，主要针对租金/押金 payment_status  '缴费状态：0未缴费 1已缴费'
        //获取已经缴费的数据
        Long normaltotal = jdbcTemplate.queryForObject(count + where + " and b.payment_status=1 and b.payment_time BETWEEN b.start_time AND b.end_time ", Long.class, args.toArray());
        //获取未缴费的数据
        Long normalNoPaytotal = jdbcTemplate.queryForObject(count + where + " and b.payment_status=0 and DATE_FORMAT(now(),'%Y%m%d') < DATE_FORMAT(b.end_time,'%Y%m%d') ", Long.class, args.toArray());
        //获取预期未缴费的数据
        Long noPaytotal = jdbcTemplate.queryForObject(count + where + " and b.payment_status=0 and DATE_FORMAT(now(),'%Y%m%d') > DATE_FORMAT(b.end_time,'%Y%m%d') ", Long.class, args.toArray());


        //查询分页数据
        String limitWhere = " ORDER BY b.create_time desc limit ? offset ?  ";
        //设置从第几条查询
        args.add(pageSize);
        //设置查询多少条数据
        args.add(pageNumber * pageSize);
        //存储已经缴费的数据
        List<Map<String, Object>> normaldata = jdbcTemplate.queryForList(SQL + where + " and b.payment_status=1 and b.payment_time BETWEEN b.start_time AND b.end_time " + limitWhere, args.toArray());
        //存储未缴费的数据
        List<Map<String, Object>> normalNoPayData = jdbcTemplate.queryForList(SQL + where + " and b.payment_status=0 and DATE_FORMAT(now(),'%Y%m%d') < DATE_FORMAT(b.end_time,'%Y%m%d') " + limitWhere, args.toArray());
        //存储预期未缴费的数据
        List<Map<String, Object>> noPayData = jdbcTemplate.queryForList(SQL + where + " and b.payment_status=0 and DATE_FORMAT(now(),'%Y%m%d') > DATE_FORMAT(b.end_time,'%Y%m%d') " + limitWhere, args.toArray());

        List<String> matchUserIds = new ArrayList<>(); //承租人
        List<String> communityIds = new ArrayList<>();//小区
        List<Long> buildingNums = new ArrayList<>(); //楼栋
        List<Long> units = new ArrayList<>(); //单元
        //遍历存储已经缴费的数据
        normaldata.forEach(m -> {
            matchUserIds.add((String) m.get("match_user_id"));
            communityIds.add((String) m.get("community_id"));
            buildingNums.add((Long) m.get("building_num"));
            units.add((Long) m.get("unit"));
        });
        //遍历存储未缴费的数据
        normalNoPayData.forEach(m -> {
            matchUserIds.add((String) m.get("match_user_id"));
            communityIds.add((String) m.get("community_id"));
            buildingNums.add((Long) m.get("building_num"));
            units.add((Long) m.get("unit"));
        });
        //遍历存储预期未缴费的数据
        noPayData.forEach(m -> {
            matchUserIds.add((String) m.get("match_user_id"));
            communityIds.add((String) m.get("community_id"));
            buildingNums.add((Long) m.get("building_num"));
            units.add((Long) m.get("unit"));
        });
        //查询出所有承租人缴费的数据
        List<AppUser> appUserList = appUserRepository.findByIdOrEnterpriseIn(matchUserIds, matchUserIds);
        //查询出所有小区缴费的数据
        List<Community> communityList = communityRepository.findByIdIn(communityIds);
        //查询出所有楼栋缴费的数据
        List<CommunityBuild> communityBuildList = communityBuildRepository.findByIdIn(buildingNums);
        //查询出所有单元缴费的数据
        List<CommunityBuildUnit> communityBuildUnitList = communityBuildUnitRepository.findByIdIn(units);

        //遍历已经缴费的数据
        for (Map<String, Object> m : normaldata) {
            // cost  '费用 单位分'
            Integer c = (Integer) m.get("cost");
            BigDecimal cost = new BigDecimal(c);
            //计算出正常欠费金额，因为后端单位是分，前端单位是元，所以需要把数据除以100
            m.put("cost", cost.divide(new BigDecimal(100), 2, RoundingMode.HALF_UP));
            //遍历所有承租人缴费的数据
            for (AppUser user : appUserList) {
                //根据个人用户ID对比出对应的数据
                if (m.get("match_user_id").equals(user.getId())) {
                    m.put("match_user_phone", user.getPhone());
                    m.put("match_user_name", user.getName());
                }
                //根据企业ID对比出对应的数据
                if (user.getEnterpriseUserId() != null && user.getEnterpriseUserId().equals(m.get("match_user_id"))) {
                    m.put("match_user_phone", user.getPhone());
                    m.put("match_user_name", user.getName());
                }
            }
            //遍历出所有小区缴费的数据
            for (Community community : communityList) {
                String communityId = (String) m.get("community_id");
                if (community.getId().equals(communityId)) {
                    m.put("community_name", community.getName());
                }
            }
            //遍历出所有楼栋缴费的数据
            for (CommunityBuild communityBuild : communityBuildList) {
                Long buildingNum = (Long) m.get("building_num");
                if (communityBuild.getId().equals(buildingNum)) {
                    m.put("building_name", communityBuild.getName());
                }
            }
            //遍历出所有单元缴费的数据
            for (CommunityBuildUnit communityBuildUnit : communityBuildUnitList) {
                Long unit = (Long) m.get("unit");
                if (communityBuildUnit.getId().equals(unit)) {
                    m.put("unit_name", communityBuildUnit.getName());
                }
            }

        }
        //遍历未缴费的数据
        for (Map<String, Object> m : normalNoPayData) {
            // cost  '费用 单位分'
            Integer c = (Integer) m.get("cost");
            BigDecimal cost = new BigDecimal(c);
            //计算出正常欠费金额，因为后端单位是分，前端单位是元，所以需要把数据除以100
            m.put("cost", cost.divide(new BigDecimal(100), 2, RoundingMode.HALF_UP));
            //遍历所有承租人缴费的数据
            for (AppUser user : appUserList) {
                //根据个人用户ID对比出对应的数据
                if (m.get("match_user_id").equals(user.getId())) {
                    m.put("match_user_phone", user.getPhone());
                    m.put("match_user_name", user.getName());
                }
                //根据企业ID对比出对应的数据
                if (user.getEnterpriseUserId() != null && user.getEnterpriseUserId().equals(m.get("match_user_id"))) {
                    m.put("match_user_phone", user.getPhone());
                    m.put("match_user_name", user.getName());
                }
            }
            //遍历出所有小区缴费的数据
            for (Community community : communityList) {
                String communityId = (String) m.get("community_id");
                if (community.getId().equals(communityId)) {
                    m.put("community_name", community.getName());
                }
            }
            //遍历出所有楼栋缴费的数据
            for (CommunityBuild communityBuild : communityBuildList) {
                Long buildingNum = (Long) m.get("building_num");
                if (communityBuild.getId().equals(buildingNum)) {
                    m.put("building_name", communityBuild.getName());
                }
            }
            //遍历出所有单元缴费的数据
            for (CommunityBuildUnit communityBuildUnit : communityBuildUnitList) {
                Long unit = (Long) m.get("unit");
                if (communityBuildUnit.getId().equals(unit)) {
                    m.put("unit_name", communityBuildUnit.getName());
                }
            }

        }
        //遍历出所有预期未缴费的数据
        for (Map<String, Object> m : noPayData) {
            // cost  '费用 单位分'
            Integer c = (Integer) m.get("cost");
            BigDecimal cost = new BigDecimal(c);
            //计算出正常欠费金额，因为后端单位是分，前端单位是元，所以需要把数据除以100
            m.put("cost", cost.divide(new BigDecimal(100), 2, RoundingMode.HALF_UP));

            m.put("overTime", daysBetween((Date) m.get("end_time"), new Date()));
            //遍历所有承租人缴费的数据
            for (AppUser user : appUserList) {
                //根据个人用户ID对比出对应的数据
                if (user.getId().equals(m.get("match_user_id"))) {
                    m.put("match_user_phone", user.getPhone());
                    m.put("match_user_name", user.getName());
                }
                //根据企业ID对比出对应的数据
                if (user.getEnterpriseUserId() != null && user.getEnterpriseUserId().equals(m.get("match_user_id"))) {
                    m.put("match_user_phone", user.getPhone());
                    m.put("match_user_name", user.getName());
                }
            }
            //遍历出所有小区缴费的数据
            for (Community community : communityList) {
                String communityId = (String) m.get("community_id");
                if (community.getId().equals(communityId)) {
                    m.put("community_name", community.getName());
                }
            }
            //遍历出所有楼栋缴费的数据
            for (CommunityBuild communityBuild : communityBuildList) {
                Long buildingNum = (Long) m.get("building_num");
                if (communityBuild.getId().equals(buildingNum)) {
                    m.put("building_name", communityBuild.getName());
                }
            }
            //遍历出所有单元缴费的数据
            for (CommunityBuildUnit communityBuildUnit : communityBuildUnitList) {
                Long unit = (Long) m.get("unit");
                if (communityBuildUnit.getId().equals(unit)) {
                    m.put("unit_name", communityBuildUnit.getName());
                }
            }

        }

        // type :  1 正常缴费数据, 2 正常欠费数据, 3 逾期欠费数据

        List<Map<String, Object>> data = new ArrayList<>();
        // 表格头
        Map<String, String> mapHead = new LinkedHashMap<>();
        // 文件名
        String fileName = "";
        //根据type查询出对应的数据;1 正常缴费数据
        if (type.intValue() == 1) {
            //存储正常缴费的数据
            data.addAll(normaldata);
            //遍历正常缴费的数据
            for (Map<String, Object> m : data) {

                String information = (String) m.get("community_name") + m.get("building_name") + m.get("unit_name") + m.get("room_number");
                m.put("house_information", information);

                int chargeType = (Integer) m.get("charge_type");
                //判读存储的数据是1租金 还是2押金
                m.put("charge_type_name", chargeType == 1 ? "租金" : "押金"); //1租金 2押金
                Object date = m.get("payment_time");
                if (date != null) {
                    String d = new SimpleDateFormat("yyyy-MM-dd").format((Date) date);
                    m.put("payment_time", d);
                }

            }

            mapHead.put("房屋ID", "house_id");
            mapHead.put("房屋信息", "house_information");
            mapHead.put("承租人", "match_user_name");
            mapHead.put("手机号", "match_user_phone");
            mapHead.put("费用类型", "charge_type_name");
            mapHead.put("费用金额 ¥", "cost");
            mapHead.put("缴费时间", "payment_time");

            fileName = "正常缴费数据" + System.currentTimeMillis();

        }
        //根据type查询出对应的数据;2 正常欠费数据
        if (type.intValue() == 2) {
            //存储正常欠缴费的数据
            data.addAll(normalNoPayData);
            //遍历正常欠费的数据
            for (Map<String, Object> m : data) {

                String information = (String) m.get("community_name") + m.get("building_name") + m.get("unit_name") + m.get("room_number");
                m.put("house_information", information);

                int chargeType = (Integer) m.get("charge_type");
                //判读存储的数据是1租金 还是2押金
                m.put("charge_type_name", chargeType == 1 ? "租金" : "押金"); //1租金 2押金

                Object date = m.get("should_pay_time");
                if (date != null) {
                    String d = new SimpleDateFormat("yyyy-MM-dd").format((Date) date);
                    m.put("should_pay_time", d);
                }

            }

            mapHead.put("房屋ID", "house_id");
            mapHead.put("房屋信息", "house_information");
            mapHead.put("承租人", "match_user_name");
            mapHead.put("手机号", "match_user_phone");
            mapHead.put("费用类型", "charge_type_name");
            mapHead.put("费用金额 ¥", "cost");
            mapHead.put("应缴费时间", "should_pay_time");

            fileName = "正常欠费数据" + System.currentTimeMillis();
        }
        //根据type查询出对应的数据;3 逾期欠费数据
        if (type.intValue() == 3) {
            //存储逾期欠费数据
            data.addAll(noPayData);
            //遍历逾期欠费数据
            for (Map<String, Object> m : data) {

                String information = (String) m.get("community_name") + m.get("building_name") + m.get("unit_name") + m.get("room_number");
                m.put("house_information", information);

                int chargeType = (Integer) m.get("charge_type");
                //判读存储的数据是1租金 还是2押金
                m.put("charge_type_name", chargeType == 1 ? "租金" : "押金"); //1租金 2押金

                Object da = m.get("should_pay_time");
                if (da != null) {
                    String d = new SimpleDateFormat("yyyy-MM-dd").format((Date) da);
                    m.put("should_pay_time", d);
                }

                Object date = m.get("overTime");
                if (date != null) {
                    String d = new SimpleDateFormat("yyyy-MM-dd").format((Date) date);
                    m.put("overTime", d);
                }

            }

            mapHead.put("房屋ID", "house_id");
            mapHead.put("房屋信息", "house_information");
            mapHead.put("承租人", "match_user_name");
            mapHead.put("手机号", "match_user_phone");
            mapHead.put("费用类型", "charge_type_name");
            mapHead.put("费用金额 ¥", "cost");
            mapHead.put("应缴费时间", "should_pay_time");
            mapHead.put("逾期时间", "overTime");

            fileName = "逾期欠费数据" + System.currentTimeMillis();
        }

        //使用Export2Util工具进行导出
        Workbook workbook = Export2Util.getExcel4(data, mapHead);
        //导出格式
        response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");

        // 导出文件下载
        DownloadUtil downloadUtil = new DownloadUtil();
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        workbook.write(bos);
        //导出的格式为.xlsx
        downloadUtil.download(bos, response, URLEncoder.encode(fileName + ".xlsx", "utf-8"));
        // 关闭
        workbook.close();
        //跟前端响应一个成功
        return ResultBean.successfulResult(null);
    }
    /**
     * 批量房源审核
     * @param vo 公租房实体类
     * @return
     */
    public ResultBean<Object> batchHouseAudit(BatchHouseAuditVO vo) {
        try {
            //获取多个公租房ID
            List<String> houseIds = vo.getHouseIds();
            //获取审批结果
            Integer type = vo.getType();
            //判断是否有数据
            if (houseIds.isEmpty()) {
                return ResultBean.failedResultOfParamWithMsg("公租房id为空");
            }
            //判断是否有数据，都审批怎么会没有传数据过来，这里的逻辑有问题
            if (type == null) {
                return ResultBean.failedResultOfParamWithMsg("审批结果为空");
            }
            //通过遍历众多公租房ID，查询对应公租房数据
            for (String houseId : houseIds) {
                Optional<House> houseOptional = houseRepository.findById(houseId);
                //判断是否有数据
                if (!houseOptional.isPresent()) {
                    return ResultBean.failedResultWithMsg("公租房不存在");
                }
                House house = houseOptional.get();

                if (type == 1) {
                    // 修改房源审核状态
                    house.setAuditStatus(HouseAuditStatusEnum.AUDIT.getValue());
                    //如果该房屋有轮候配租记录，修改成可配租状态
                    List<WaitingRentRecord> waitingRentRecords = waitingRentRecordRepository.
                            findByOrgIdAndResidenceCommunityIdAndStatus(house.getOrgId(), house.getResidenceCommunityId(), 0);
                    if (!waitingRentRecords.isEmpty()) {
                        house.setMatchStatus(1);
                    }
                } else {
                    house.setAuditStatus(HouseAuditStatusEnum.NO_PASS.getValue());
                }
                //修改，保存数据
                houseRepository.save(house);

                //更新待办事项
                List<BacklogItem> items = backlogItemRepository.findByHouseIdAndTypeAndHandleStatus(houseId, BacklogItemTypeEnum.HOUSE_SAVE.getValue(), 0);
                if (!items.isEmpty()) {
                    //遍历数据
                    for (BacklogItem item : items) {
                        Optional<BacklogItem> itemOpt = backlogItemRepository.findById(item.getId());
                        if (!itemOpt.isPresent()) {
                            return ResultBean.failedResultWithMsg("待办事项不存在");
                        }
                        //设置数据
                        BacklogItem backlogItem = itemOpt.get();
                        backlogItem.setBusinessStatus("已审批");
                        backlogItem.setHandleStatus(1);// 1已读/已处理
                        backlogItem.setHandlerTime(new Date());
                        backlogItem.setAuditStatus(type);
                    }
                    //把数据存储保存在数据库
                    backlogItemRepository.saveAll(items);
                }
            }
            //把结果返回给前端页面
            return ResultBean.successfulResult(null);
        } catch (Exception e) {
            //记录保存日志
            log.error(e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }
    }


    /**
     * 保障房-数据
     */
    /**
     * 公租房管理模块首页公租房-数据 实现了公租房管理模块首页数据展示
     * @param accessToken 权限鉴定
     * @return
     */
    public ResultBean<Map<String, Object>> timePageData(String accessToken) {
        //获取登录用户的权限
        TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
        //查询出公租房中有多少数据
        String count = " select COUNT(1) from saas_house h  ";
        //判断登录的用户是否有权限，如果没有权限不让使用
        if (tokenRedisVo == null) {
            return ResultBean.failedResultOfToken();
        }
        //获取用户的ID
        Long enterpriseId = tokenRedisVo.getEnterpriseId();
        //根据用户ID查询出用户层级权限
        DepartmentHierarchy hierarchy = departmentHierarchyRepository.findByOrgIdAndStatus(enterpriseId, 1);
        if (hierarchy == null) {
            return ResultBean.failedResultWithMsg("用户所属机构未设置层级架构");
        }
        //获取机构下有多少保障房                                          and h.housemate_id is null 不要查询出合租房产中的子房产
        StringBuilder where = new StringBuilder(" WHERE h.status = 1 and h.house_nature = 2 and h.housemate_id is null  ");   //house_nature 性质:1公租房、2保障房、3出租房、4自用房、5单位宿舍
        //权限过滤
        Long DepRefId = hierarchy.getId();
        List<DepartmentEnterpriseTagRef> refs = departmentEnterpriseTagRefRepository.findByDepRefId(DepRefId);
        List<Long> enterpriseTagIds = refs.stream().map(DepartmentEnterpriseTagRef::getTagId).distinct().collect(Collectors.toList());//用户企业标签

        //1、政府机构，根据区域标签所对应区域id查
        Long areaTagId = hierarchy.getTagId();//区域标签
        Long areaId = null;
        if (enterpriseTagIds.contains(3L)) {
            areaId = hierarchy.getAreaId();//区域id
        }
        String areaTag = "province_id";
        if (areaTagId == 1) {
            areaTag = "province_id";
        } else if (areaTagId == 2) {
            areaTag = "city_id";
        } else if (areaTagId == 3) {
            areaTag = "district_id";
        } else if (areaTagId == 4) {
            areaTag = "street_id";
        } else if (areaTagId == 5) {
            areaTag = "community_id";
        }

        Long hierarchyOrgId = null;
        if (enterpriseTagIds.contains(2L) || enterpriseTagIds.contains(8L)) {
            hierarchyOrgId = hierarchy.getOrgId();
        }
        Long unitLesseeId = null;
        if (enterpriseTagIds.contains(7L)) {
            unitLesseeId = hierarchy.getOrgId();
        }

        //3、物业，查已绑定该物业的小区下的所有房产
        List<String> residenceCommunityIds = new ArrayList<>();
        if (enterpriseTagIds.contains(4L)) {
            List<Community> communities = communityRepository.findByOrgIdAndStatus(enterpriseId, 1);
            residenceCommunityIds = communities.stream().map(Community::getId).distinct().collect(Collectors.toList());//小区id
        }

        List<Object> args = new ArrayList<>();

        //拼接标签查询权限
        where.append(" and (h." + areaTag + " = ? or h.org_id = ? or h.unit_lessee_id = ? or h.residence_community_id in ");
        args.add(areaId);
        args.add(hierarchyOrgId);
        args.add(unitLesseeId);
        if (residenceCommunityIds != null && !residenceCommunityIds.isEmpty()) {
            where.append(SqlUtils.foreachIn(residenceCommunityIds.size()) + ")");
            args.addAll(residenceCommunityIds);
        } else {
            where.append("(null))");
        }

        //权限过滤
        //String communityIds = null;
        //List<Map<String, Object>> communityList = appCommunityService.findCommunity(accessToken).getResultData();
        //if (communityList != null && !communityList.isEmpty()) {
        //    communityIds = communityList.stream().map(map -> "\'" + map.get("communityId")
        //            .toString() + "\'" ).collect(Collectors.joining(","));
        //}
        //where.append(" and h.residence_community_id in ( " + communityIds + " )");

        //where.append(" and (h.org_id = ? or h.house_property_id = ? ) ");
        //args.add(hierarchy.getOrgId());
        //args.add(hierarchy.getOrgId());

        //不需要时间筛选
        // where.append("and h.create_time >= ?");
        // args.add(startTime);
        // where.append("and h.create_time <= ?");
        // args.add(endTime);

        Map<String, Object> map = new HashMap<>();

        //保障房总套数
        Long total = jdbcTemplate.queryForObject(count + where, args.toArray(), Long.class);
        //已出租保障房总套数  lease_status '出租状态：0未出租 1已出租 2已过期'
        Long matchNum = jdbcTemplate.queryForObject(count + where + " and (h.lease_status= 1 OR h.lease_status= 3 OR h.lease_status= 4 ) ", args.toArray(), Long.class);

        //空置房总套数
        Long unMatchNum = total - matchNum;

        //把数据封装进map里面
        map.put("total", total);
        map.put("matchNum", matchNum);
        map.put("unMatchNum", unMatchNum);
        //把数据结果传给前端页面
        return ResultBean.successfulResult(map);
    }

    /**
     * 非公租房管理模块首页非公租房-数据 首页出租房-数据统计
     * @param accessToken 权限鉴定
     * @return
     */
    public ResultBean<Map<String, Object>> rentalTimePageData(String accessToken) {
        //获取登录用户的权限
        TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
        //查询出非公租房中有多少数据
        String count = " select COUNT(1) from saas_house h  ";
        //判断登录的用户是否有权限，如果没有权限不让使用
        if (tokenRedisVo == null) {
            return ResultBean.failedResultOfToken();
        }
        //获取用户的ID
        Long enterpriseId = tokenRedisVo.getEnterpriseId();
        //根据用户ID查询出用户层级权限
        DepartmentHierarchy hierarchy = departmentHierarchyRepository.findByOrgIdAndStatus(enterpriseId, 1);
        if (hierarchy == null) {
            return ResultBean.failedResultWithMsg("用户所属机构未设置层级架构");
        }
        //获取机构下有多少非公租房
        StringBuilder where = new StringBuilder(" WHERE h.status = 1 and h.house_nature = 3 and h.housemate_id is null ");   //house_nature 性质:1公租房、2保障房、3出租房、4自用房、5单位宿舍
        //权限过滤
        Long DepRefId = hierarchy.getId();
        List<DepartmentEnterpriseTagRef> refs = departmentEnterpriseTagRefRepository.findByDepRefId(DepRefId);
        List<Long> enterpriseTagIds = refs.stream().map(DepartmentEnterpriseTagRef::getTagId).distinct().collect(Collectors.toList());//用户企业标签

        //1、政府机构，根据区域标签所对应区域id查
        Long areaTagId = hierarchy.getTagId();//区域标签
        Long areaId = null;
        if (enterpriseTagIds.contains(3L)) {
            areaId = hierarchy.getAreaId();//区域id
        }
        String areaTag = "province_id";
        if (areaTagId == 1) {
            areaTag = "province_id";
        } else if (areaTagId == 2) {
            areaTag = "city_id";
        } else if (areaTagId == 3) {
            areaTag = "district_id";
        } else if (areaTagId == 4) {
            areaTag = "street_id";
        } else if (areaTagId == 5) {
            areaTag = "community_id";
        }

        Long hierarchyOrgId = null;
        if (enterpriseTagIds.contains(2L) || enterpriseTagIds.contains(8L)) {
            hierarchyOrgId = hierarchy.getOrgId();
        }
        Long unitLesseeId = null;
        if (enterpriseTagIds.contains(7L)) {
            unitLesseeId = hierarchy.getOrgId();
        }

        //3、物业，查已绑定该物业的小区下的所有房产
        List<String> residenceCommunityIds = new ArrayList<>();
        if (enterpriseTagIds.contains(4L)) {
            List<Community> communities = communityRepository.findByOrgIdAndStatus(enterpriseId, 1);
            residenceCommunityIds = communities.stream().map(Community::getId).distinct().collect(Collectors.toList());//小区id
        }

        List<Object> args = new ArrayList<>();

        //拼接标签查询权限
        where.append(" and (h." + areaTag + " = ? or h.org_id = ? or h.unit_lessee_id = ? or h.residence_community_id in ");
        args.add(areaId);
        args.add(hierarchyOrgId);
        args.add(unitLesseeId);
        if (residenceCommunityIds != null && !residenceCommunityIds.isEmpty()) {
            where.append(SqlUtils.foreachIn(residenceCommunityIds.size()) + ")");
            args.addAll(residenceCommunityIds);
        } else {
            where.append("(null))");
        }

        ////权限过滤
        //String communityIds = null;
        //List<Map<String, Object>> communityList = appCommunityService.findCommunity(accessToken).getResultData();
        //if (communityList != null && !communityList.isEmpty()) {
        //    communityIds = communityList.stream().map(map -> "\'" + map.get("communityId")
        //            .toString() + "\'" ).collect(Collectors.joining(","));
        //}
        //where.append(" and h.residence_community_id in ( " + communityIds + " )");

        //List<Object> args = new ArrayList<>();
        //where.append(" and (h.org_id = ? or h.house_property_id = ? )");
        //args.add(hierarchy.getOrgId());
        //args.add(hierarchy.getOrgId());
        Map<String, Object> map = new HashMap<>();

        //出租房总套数
        Long total = jdbcTemplate.queryForObject(count + where, args.toArray(), Long.class);
        //已出租总套数  lease_status '配租状态：0未出租 1已出租'
        Long matchNum = jdbcTemplate.queryForObject(count + where + " and (h.lease_status= 1 OR h.lease_status= 3 OR h.lease_status= 4 )", args.toArray(), Long.class);
        //空置房总套数
        Long unMatchNum = total - matchNum;
        //已出租房整租
        Long entireNum = jdbcTemplate.queryForObject(count + where + " and h.match_type=1  and (h.lease_status= 1 OR h.lease_status= 3 OR h.lease_status= 4 )  ", args.toArray(), Long.class);
        //已出租房合租
        Long sharedNum = jdbcTemplate.queryForObject(count + where + " and h.match_type=2  and (h.lease_status= 1 OR h.lease_status= 3 OR h.lease_status= 4 ) ", args.toArray(), Long.class);
        //把数据封装进map里面
        map.put("total", total);
        map.put("matchNum", matchNum);
        map.put("unMatchNum", unMatchNum);
        map.put("entireNum", entireNum);
        map.put("sharedNum", sharedNum);
        //把数据结果传给前端页面
        return ResultBean.successfulResult(map);
    }

    /**
     * 房管理模块首页其他房产-数据
     * @param accessToken 权限鉴定
     * @return
     */
    public ResultBean<List<Map<String, Object>>> allTimePageData(String accessToken) {
        //获取登录用户的权限
        TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
        //查询出非公租房中有多少数据
        String count = " select COUNT(1) from saas_house h  ";
        //判断登录的用户是否有权限，如果没有权限不让使用
        if (tokenRedisVo == null) {
            return ResultBean.failedResultOfToken();
        }
        //获取用户的ID
        Long enterpriseId = tokenRedisVo.getEnterpriseId();
        //根据用户ID查询出用户层级权限
        DepartmentHierarchy hierarchy = departmentHierarchyRepository.findByOrgIdAndStatus(enterpriseId, 1);
        if (hierarchy == null) {
            return ResultBean.failedResultWithMsg("用户所属机构未设置层级架构");
        }
        //获取机构下有多少非公租房
        StringBuilder where = new StringBuilder(" WHERE h.status = 1 and h.housemate_id is null ");   //house_nature 性质:1公租房、2保障房、3出租房、4自用房、5单位宿舍

        Long DepRefId = hierarchy.getId();
        List<DepartmentEnterpriseTagRef> refs = departmentEnterpriseTagRefRepository.findByDepRefId(DepRefId);
        List<Long> enterpriseTagIds = refs.stream().map(DepartmentEnterpriseTagRef::getTagId).distinct().collect(Collectors.toList());//用户企业标签

        //1、政府机构，根据区域标签所对应区域id查
        Long areaTagId = hierarchy.getTagId();//区域标签
        Long areaId = null;
        if (enterpriseTagIds.contains(3L)) {
            areaId = hierarchy.getAreaId();//区域id
        }
        String areaTag = "province_id";
        if (areaTagId == 1) {
            areaTag = "province_id";
        } else if (areaTagId == 2) {
            areaTag = "city_id";
        } else if (areaTagId == 3) {
            areaTag = "district_id";
        } else if (areaTagId == 4) {
            areaTag = "street_id";
        } else if (areaTagId == 5) {
            areaTag = "community_id";
        }

        Long hierarchyOrgId = null;
        if (enterpriseTagIds.contains(2L) || enterpriseTagIds.contains(8L)) {
            hierarchyOrgId = hierarchy.getOrgId();
        }
        Long unitLesseeId = null;
        if (enterpriseTagIds.contains(7L)) {
            unitLesseeId = hierarchy.getOrgId();
        }

        //3、物业，查已绑定该物业的小区下的所有房产
        List<String> residenceCommunityIds = new ArrayList<>();
        if (enterpriseTagIds.contains(4L)) {
            List<Community> communities = communityRepository.findByOrgIdAndStatus(enterpriseId, 1);
            residenceCommunityIds = communities.stream().map(Community::getId).distinct().collect(Collectors.toList());//小区id
        }

        List<Object> args = new ArrayList<>();

        //拼接标签查询权限
        where.append(" and (h." + areaTag + " = ? or h.org_id = ? or h.unit_lessee_id = ? or h.residence_community_id in ");
        args.add(areaId);
        args.add(hierarchyOrgId);
        args.add(unitLesseeId);
        if (residenceCommunityIds != null && !residenceCommunityIds.isEmpty()) {
            where.append(SqlUtils.foreachIn(residenceCommunityIds.size()) + ")");
            args.addAll(residenceCommunityIds);
        } else {
            where.append("(null))");
        }

        //权限过滤
        //String communityIds = null;
        //List<Map<String, Object>> communityList = appCommunityService.findCommunity(accessToken).getResultData();
        //if (communityList != null && !communityList.isEmpty()) {
        //    communityIds = communityList.stream().map(map -> "\'" + map.get("communityId")
        //            .toString() + "\'" ).collect(Collectors.joining(","));
        //}
        //where.append(" and h.residence_community_id in ( " + communityIds + " )");

        //where.append("  and (h.org_id = ? or h.house_property_id = ? ) ");
        //args.add(hierarchy.getOrgId());
        //args.add(hierarchy.getOrgId());

        //List<Object> args = new ArrayList<>();
        List<Map<String, Object>> mapList = new ArrayList<>();
        //查询出房屋属性
        Iterable<HouseType> all = houseTypeRepository.findAll();
        for (HouseType houseType : all) {
            Long id = houseType.getId();
            if (id != 1 && id != 2 && id != 3) {
                HashMap<String, Object> map = new HashMap<>();
                args.add(id);
                String type = houseType.getType() + "总套数";
                Long num = jdbcTemplate.queryForObject(count + where + "and h.house_nature = ? ", args.toArray(), Long.class);
                map.put("type",type);
                map.put("num",num);
                mapList.add(map);
                args.remove(args.size() -1);
            }
        }
        //出租房总套数
        //Long rentalNum = jdbcTemplate.queryForObject(count + where + " and h.house_nature = 3  ", args.toArray(), Long.class);
        //公租房总数
        //Long publicNum = jdbcTemplate.queryForObject(count + where + " and h.house_nature = 2 ", args.toArray(), Long.class);
        //其他房屋总数
        //Long restsNum = jdbcTemplate.queryForObject(count + where + " and h.house_nature != 1 and h.house_nature != 2 and h.house_nature != 3 ", args.toArray(), Long.class);
        //把数据封装进map里面
        //map.put("rentalNum", rentalNum);
        //map.put("publicNum", publicNum);
        //map.put("restsNum", restsNum);
        //把数据结果传给前端页面
        return ResultBean.successfulResult(mapList);
    }
}

